Postgre SQL：値の設定ルールが変わったために、ソートの条件を見直し

今年からルールが変わったために対応していました。
f:id:AJYA:20141024053859j:plain
photo credit: therefromhere via photopin cc

昨年まではあるカラムに対して、A-01～99/B-01～99/C-01～99という値を割り振られていました。
今年はお客さんの都合で、AA-CC-01～99/AA-DD-01～99/AB-CC-01～99/AB-DD-01～99/AC-01～99という値が割り振られました。

システム上の動作は問題はありませんでしたが、ソートの順序だけ正しくありませんでした。

昨年までの値は、以下のSQLでA-01～99→B-01～99→C-01～99の順番にソートしていました。

SELECT
SUBSTRING( order FROM '[a-zA-Z]+') AS col1,
CAST( SUBSTRING( order FROM '[0-9]+') AS INTEGER) AS col2,
FROM table1
ORDER BY col1 ASC, col2 ASC;

今年の値では、上記SQLではAA-CC-01がAAと01、AA-DD-01がAAと01でソートしてしまうため、
AA-CC-01
AA-DD-01
AA-CC-02
AA-DD-02
AC-01
の順番でソートされていました。

ソートを
AA-CC-01
AA-CC-02
AA-DD-01
AA-DD-02
AC-01
の順番にするためには、1個目の-の後の部分(CC/DDの部分)を取り出して、数値だったらダミーの値に置き換えて、そうでなければそのままソートに使う方法を思いついたので、実装したのが以下のSQLです。

SELECT
SUBSTRING( order FROM '[a-zA-Z]+') AS col1,
CASE 
    WHEN CAST( SUBSTRING( split_part(order, '-', 2) FROM '[0-9]+') AS INTEGER) IS NOT NULL THEN 'ZZ'
    ELSE split_part(order, '-', 2)
END AS col3,
CAST( SUBSTRING( order FROM '[0-9]+') AS INTEGER) AS col2,
FROM table1
ORDER BY col1 ASC, col3 ASC, col2 ASC;