SQLで、
A1
A2
A10
などの文字列があるカラムをこの順番でORDERする方法を知りたいです。

このカラムは text なので、通常のソートだと
A1
A10
A2
となってしまいます。


文字列の種類は、文字を S、 数字部分を n とすると
S n それぞれ1桁以上あります。なので、
SSSnnnnn という項目もありますし、
SSSSSSSSSn という項目も存在します。

環境はPostgreSQL8.1です。
よろしくお願いします。

回答の条件
  • 1人50回まで
  • 登録:2006/03/29 01:09:54
  • 終了:2006/03/31 02:09:52

ベストアンサー

id:bonlife No.3

回答回数421ベストアンサー獲得回数752006/03/29 21:07:59

ポイント100pt

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

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

再回答いたします。

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を作って自然順ソートを実現している人もいるようです。

id:kamiochiai

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

OKでした。

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

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

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

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

2006/03/31 02:08:59

その他の回答(2件)

id:bonlife No.1

回答回数421ベストアンサー獲得回数752006/03/29 08:04:59

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

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

です。

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

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

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

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

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

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

id:kamiochiai

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

具体的には、

A123

AB1234

A12345

となってしまいました。

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

2006/03/29 19:51:48
id:bonlife No.2

回答回数421ベストアンサー獲得回数752006/03/29 11:29:35

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

文字列部分も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があった場合に、順序が逆にならない方が良いと考えたためです。

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

id:kamiochiai

ありがとうございます!

完璧です。

2006/03/29 16:41:26
id:bonlife No.3

回答回数421ベストアンサー獲得回数752006/03/29 21:07:59ここでベストアンサー

ポイント100pt

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

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

再回答いたします。

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を作って自然順ソートを実現している人もいるようです。

id:kamiochiai

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

OKでした。

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

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

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

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

2006/03/31 02:08:59

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません