- 環境
- SQL*Plus: Release 12.2.0.1.0 Production
- Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
やりたいこと
Excelのセル位置みたいな値とはA1とかBA25みたいなやつです。
「A >...> Z > AA」「1 >...> 9 > 10 > 11」的にソートしたいです。
「A > AA > B > BB」とか「1 > 11 > 2 > 21」みたいに並べたくないです。
こういう風にしたくない
SQL> select * from cell_info order by cell;
CELL CONTENTS
------------ ------------------------------------
A1 peach
A11 mandarin --> 「1」の次は「11」じゃなくて「2」がいい
A2 pumpkin
A25 plum
AA1 cherry --> 「A」の次は「AA」じゃなくて「B」がいい
AA11 pomegranate
AA2 loquat
AA25 kumquat
AC1 turnip
AC11 banana
AC2 grape
AC25 melon
B1 apple
B11 orange
B2 persimmon
B25 strawberry
16行が選択されました。
Excelのセル位置みたいな値をソートする方法
結論
-- 列を第1キーにする場合
SQL> select * from cell_info order by length(regexp_substr(cell,'[A-Z]+')),regexp_substr(cell,'[A-Z]+'),to_number(regexp_substr(cell,'[0-9]+'));
CELL CONTENTS
------------ ------------------------------------
A1 peach
A2 pumpkin
A11 mandarin
A25 plum
B1 apple
B2 persimmon
B11 orange
B25 strawberry
AA1 cherry
AA2 loquat
AA11 pomegranate
AA25 kumquat
AC1 turnip
AC2 grape
AC11 banana
AC25 melon
16行が選択されました。
-- 行を第1キーにする場合
SQL> select * from cell_info order by to_number(regexp_substr(cell,'[0-9]+')),length(regexp_substr(cell,'[A-Z]+')),regexp_substr(cell,'[A-Z]+');
CELL CONTENTS
------------ ------------------------------------
A1 peach
B1 apple
AA1 cherry
AC1 turnip
A2 pumpkin
B2 persimmon
AA2 loquat
AC2 grape
A11 mandarin
B11 orange
AA11 pomegranate
AC11 banana
A25 plum
B25 strawberry
AA25 kumquat
AC25 melon
16行が選択されました。
方法：セル位置の文字列を分解してソートした
regexp_substr関数で文字列を分解してソートのキーにしました。
行番号は数値化しないと文字列として「1 > 11 > 2 > 21」みたいにソートされてしまいます。
SQL> select cell,length(regexp_substr(cell,'[A-Z]+')) as 列番号の文字数,regexp_substr(cell,'[A-Z]+') as 列番号の部分,to_number(regexp_substr(cell,'[0-9]+')) as 行番号の部分 from cell_info;
CELL 列番号の文字数 列番号の部分 行番号の部分
------------ -------------- ------------------------------------------------ ------------
A1 1 A 1
A11 1 A 11
A2 1 A 2
A25 1 A 25
AA1 2 AA 1
AA11 2 AA 11
AA2 2 AA 2
AA25 2 AA 25
AC1 2 AC 1
AC11 2 AC 11
AC2 2 AC 2
AC25 2 AC 25
B1 1 B 1
B11 1 B 11
B2 1 B 2
B25 1 B 25
16行が選択されました。