SQLで、 A1 A2 A10 などの文字列があるカラムをこの順番でORDERする方法を知りたいです。このカラムは text なので、通常のソートだと A1 A10 A2 となってしまいます。 ..

kamiochiai あなたも質問に答えられます！ウォッチリストに追加
状態：終了
回答数：3 / 995件
回答ポイント：100ポイント
登録：2006-03-29 01:09:54
終了：2006-03-31 02:09:52
カテゴリー：コンピュータウェブ制作

3 回答者：bonlife 2006-03-29 21:07:59 満足！ 100ポイント

文字列優先(というより自然順)で並び替える、ということでしょうか。

数値での比較を行う、という質問だと思っていましたので、その部分まで考慮できていませんでした。

再回答いたします。

SELECT *
FROM sample01
ORDER BY substring(col1 from '^[A-Z]+'), to_number(substring(col1 from '[0-9]+$'),'9999999999');

考え方としては今までと同じで文字部分(大文字アルファベットのみだと仮定)を正規表現で抜き出したもので先にソートさせるようにしました。

(正規表現の部分は修正しています。)

小文字がある場合など、適宜正規表現で指定している部分を変更してください。

これで出力結果は

  col1
--------
 A123
 A12345
 AB1234
(3 rows)

となります。

いかがでしょうか。

参考情報ですが、独自のFunctionを作って自然順ソートを実現している人もいるようです。

http://www.postgresql.org/docs/8.0/interactive/queries-order...

質問者：

kamiochiai 2006-03-31 02:08:59

再三ご回答ありがとうございました。

ＯＫでした。

以下、私的な備忘録として書いておきますが

UNIONを含むSELECT構文ではこのORDERは使用できませんでした。

なんらかのカラムを立ててそれをソートする必要があるようです。

このFUNCTIONは素敵ですね。使ってみたいと思います。

1 回答者：bonlife 2006-03-29 08:04:59 0ポイント

MySQLならテーブルをsample01、対象の列をcol1とした場合

SELECT * FROM sample01 ORDER BY CAST(substr(col1,2) AS UNSIGNED);

です。

対象の列の2文字目以降をsubstr関数で抜き出し、CASTで数値(UNSIGNED)にしたものでソートします。

PostgreSQLの場合は、CASTではなくto_numberを使えば数値化できるはずですので、以下のようにしてみてはいかがでしょうか。

http://www.postgresql.jp/document/pg813doc/html/functions-st...

SELECT * FROM sample01 ORDER BY to_number(substr(col1,2),'9999999999');

実行する環境がないので試せてませんので、エラーが出ましたら内容に応じて修正してみてください。

文字列の一部を取得する場合、PostgreSQLではsubstrではなくsubstringを使うのが一般的かもしれません。

その場合は指定する数字の順序にご注意ください。

質問者：

kamiochiai 2006-03-29 19:51:48

返信が逆になってしまうのですが、下の「完璧です!」と書いたんですが、ダメでした。

具体的には、

A123

AB1234

A12345

となってしまいました。

調べる時間が出来たんで調べてみたんですが、こういう並べ替えは PHP では natsort ( 自然順 ) を使えば出来るんですね。知らなかったです。付記しておきます。

2 回答者：bonlife 2006-03-29 11:29:35 0ポイント

上記の件、読み違えておりました。

文字列部分も1文字ではないのですね。

SQLを修正いたしましたので、ご確認ください。

SELECT *
FROM sample01
ORDER BY to_number(substring(col1 from '[0-9].*$'),'9999999999'), col1;

substring関数で正規表現を使い、数値部のみを取得し、それを型変換して数値にした上でソートの条件に使います。

(数字部分が10桁より大きい場合、適宜999..の部分に9を足してください。)

これで数字部分でソートされます。

ORDER BYの2個目にcol1と指定しているのは、たとえば、A1とB1があった場合に、順序が逆にならない方が良いと考えたためです。

数値部以外は考慮する必要ないのであれば、指定不要です。

質問者：

kamiochiai 2006-03-29 16:41:26

ありがとうございます！

完璧です。

3 回答者：bonlife 2006-03-29 21:07:59 満足！ 100ポイント

1 回答者：bonlife 2006-03-29 08:04:59 0ポイント

2 回答者：bonlife 2006-03-29 11:29:35 0ポイント

おとなり質問

この質問・回答へのトラックバック