Oracle SQLで文字列と数値が混在するカラムをソート(ORDER BY)する方法
Oracle SQLで文字列と数値が混在するカラムをソート(ORDER BY)する方法です。
テーブルの文字列型カラムに文字列と数値が混在している場合、普通に並び替えても想定したソート順、つまり自然数(数値)順になりません。
今回は テーブルの文字列型カラムに文字列と数値が混在している場合の自然数(数値)ソートの方法 を紹介します。
環境
- Oracle 11g
今回のテスト用テーブルとデータです。
CREATE TABLE EXAMPLE (
EXCODE NVARCHAR2(30) NOT NULL
);
INSERT INTO EXAMPLE VALUES('NUM1');
INSERT INTO EXAMPLE VALUES('NUM2');
INSERT INTO EXAMPLE VALUES('NUM3');
INSERT INTO EXAMPLE VALUES('NUM4');
INSERT INTO EXAMPLE VALUES('NUM10');
INSERT INTO EXAMPLE VALUES('NUM20');
INSERT INTO EXAMPLE VALUES('NUM30');
INSERT INTO EXAMPLE VALUES('NUM100');
用意したレコードは半角英字と数値の組み合わせと、数値のみとなります。
数値文字列をソートする方法
普通に「ORDER BY EXCODE」とした場合、下図のようになりますね。
これを自然数(数値)でソートするには、こういう ORDER BY 句になります。
ORDER BY TO_NUMBER( REGEXP_SUBSTR(カラム名, '[0-9]+$') )
REGEXP_SUBSTRは正規表現を使用して文字列の一部を切り出す関数です。正規表現で数値だけを切り出してソートします。
結果はこうなります。
おおおーー、自然数(数値)でソートされたー＾＾
・・・・と、実は、ここで喜んではいけません。
下記のデータを追加しましょう。
INSERT INTO EXAMPLE VALUES('CODE1');
INSERT INTO EXAMPLE VALUES('CODE2');
INSERT INTO EXAMPLE VALUES('CODE10');
すると、こうなっちゃいます。
あちゃー！数値だけ意識したので英字については規則性のない結果が・・・。
こんな時は下記のように ORDER BY 句を修正します。
ORDER BY REGEXP_SUBSTR(カラム名, '^([a-z]|[A-Z])+'), TO_NUMBER( REGEXP_SUBSTR(カラム名, '[0-9]+$') )
REGEXP_SUBSTR関数を使って、英字文字以外の切り出しと、数値だけの切り出しをしてソートします。
結果はこんな感じです。
ふむふむ、いい感じですね＾＾
まとめ
今回はOracle SQLで文字列と数値が混在するカラムをソート(ORDER BY)する方法を紹介しました。
文字列(半角英字)と数値が組み合わさったカラムとしては従業員番号などがあるでしょうか。企業によっては入社した年度を英字と数字を組み合わせて表したり、正社員と非正規社員との比較に英数字を利用したりと、様々な番号体系がありますよね。そんな規則性もある中でも複雑になったレコードを並び替えるのに、今回紹介した方法が役立つのではないかと思います。
いつも使う方法じゃないですからね、忘れたらまた見に来てください。
おつかれさまでした。