SQLのORDER BYによるソート結果がおかしいとき（文字付きの連番）

良いか悪いかは別にして、1から始まる番号に文字を付加してA1、A2、… A10 … A20のような番号を入力することもあります。たとえば、次のnoフィールドのようなイメージです。

連番に文字を付加したフィールド

このフィールドは文字列なので、noフィールドを基準にソートすると意図しない結果になります。

ソート結果が意図と違う

どうすれば良いか誰かに質問すると、おそらく「桁をそろえて入力しましょう」という答えが返ってくるでしょう。

とはいえ、入力し直せない事情もあるでしょうから、ここではデータはそのままにして、noフィールドを加工してソートする方法を紹介します。

※以下、MySQLの例です。他のデータベースでは使える関数が異なる場合があるので、適宜、読み替えてください。

A1、A2、… A10 … A20を桁をそろえるイメージは次のようになります。

A1 → 001
A2 → 002
A10 → 010
A20 → 020

このように先頭の文字を削除して数値の桁数をそろえれば、うまくソートできそうです。能書きが長くなるので、実行するSQLと実行結果を先に見せます。

SELECT * 
FROM table3
ORDER BY RIGHT( CONCAT(  '00', SUBSTRING( no, 2 ) ) , 3 )

これにより、noフィールドは意図した順にソートされます。

番号順にソートされる

使ったMySQL関数について

ここで使った関数について簡単に説明します。

処理の意図は、

です。

そのため次の関数を使っています。

この関数によりA10は次のように加工されます。

これにより、A1、A2、… A10 … A20のような文字付きの連番から桁数のそろった数値が作成されます。