HOME　>>　Tips　>>　Microsoft SQL Server

桁数が異なる数値を含む文字列を"数値を考慮して"ソートする方法

2016/5/15
文書番号：20499

数値を含む文字列を数値を考慮してソートしたいことはよくあると思います。 通常、データベースで文字列型(CHAR や VARCHAR)型の列にある数値は数値のみであっても文字列として扱われますので普通にソートすると以下のようになります。

データ
1
10
11
2
3
 →
データ
1
2
3
10
11
この場合は ORDER BY の部分でだけ数値に変換するとうまくソートできます。

SELECT データ
FROM   TAB_A
ORDER BY CONVERT(int,データ)
以下のようにデータが数値以外を含む場合は数値型に変換できませんので上記の方法は使えません。
データ
A-1
A-10
A-11
A-2
A-3
 →
データ
A-1
A-2
A-3
A-10
A-11
このような場合は 文字列の長さも考慮するとうまくソートできます。
ハイフンの後ろの文字列を取得してそれを数値に変換して判断させてもできそうですが、その方がコスト高そう...

SELECT データ
FROM   TAB_A
ORDER BY LEN(データ)
        ,データ
これは応用編ですが、ハイフンの後に英字が付くものが含まれる場合で、英字で始まるものを先にしたい場合。
データ
A-1
A-10
A-11
A-2
A-3
A-A1
A-A1
 →
データ
A-A1
A-A1
A-1
A-2
A-3
A-10
A-11
このような場合は ハイフンの後の1文字が数値かどうかを考慮するようにします。
　１．文字列の中からハイフンの位置を取得する（CHARINDEX）
　２．ハイフンの次にある一文字を取得する（SUBSTRING）
　３．その文字が数値かどうかで判断する（ISNUMERIC）
　４．文字列の長さで判断する（LEN）
　５．最後に通常のソート
というようにするとうまくソートできます。

SELECT データ
FROM   TAB_A
ORDER BY ISNUMERIC(SUBSTRING(データ,CHARINDEX('-',データ)+1,1))
        ,LEN(データ)
        ,データ
ORDER BY であまりやりすぎるとレスポンスに影響する場合があるのでその辺も考慮しながら考える必要があります。
その場合は事前にソートするための情報を列に持っておいた方がいい場合もあるかもしれません。
実際の環境に合わせて試してみてください。