SQL Serverの文字列の大小比較について
SQL Serverの文字列の大小比較、公式ドキュメントに明記されてないっぽい？ので一部調べてまとめてみました。 社内Wikiに載せたものを公開します。
間違っていたり、不足している内容があるかもしれません。もしございましたら指摘いただけると大変助かります。
基本的な比較の仕方
SQLServerでは複数の文字列の大小比較を行う場合、左から1文字づつ比較しています。 (Oracleでは上記仕様で公式ドキュメントに記載されている。SQLServerは見当たらず。(もし記載あったら教えてください・・・。))
例えば以下のクエリを実行した場合、
2文字目の'a'と'b'の比較の時点で結果が決まり、以降の文字の比較は行われません。
select case when 'aab' < 'abc' then 'o' else 'x' end /* 結果 'o' */
比較対象の文字数が異なる場合、文字数の少ない方は多い方の文字数に合わせて 内部的にスペースを末尾に足して比較しています。
例えば以下のクエリの場合、
select case when 'aabccccc' < 'abc' -- ★ then 'o' else 'x' end
実際に行っている比較としては以下と同じになります。
select case when 'aabccccc' < 'abc ' -- ★ then 'o' else 'x' end
以下のクエリの場合、「文字数が多いから①の方が大きい判定になる」というよりは、
少ない方の末尾に追加されるスペースと差分の文字(4文字目の'a')を比較して大きい判定になるイメージに近いと思われます。
select case when 'aaaaaaaa' -- ① < 'aaa' -- ② then 'o' else 'x' end /* 結果 'x' */
以下クエリの②は末尾にTabが入っているため単純に文字数だけで見ると
②の方が大きい判定になるように見えますが、
スペースとTabを比較した場合はスペースの方が大きいため、結果'x'になります。(後述しますが照合順序によって結果が変わるため注意)
select case when 'aaa' COLLATE japanese_bin -- ① < 'aaa ' COLLATE japanese_bin -- ② then 'o' else 'x' end /* 結果 'x' */
参考例
select case when 'abc' = 'abc ' then 'o' else 'x' end /* 結果 'o' */
select case when 'abcc' < 'abc z' then 'o' else 'x' end /* 結果 'x' 4文字目の'c'と' 'の比較で'c'の方が大きい判定になる。 (5文字目は'c'と'z'の比較で2つ目の方が大きいが4文字目で比較が打ち切られているので無視される。) */
照合順序について
大小比較は照合順序によって結果が変わります。
Japanese_BINの場合
例えばJapanese_BIN(バイナリ照合順序)の場合、順序はAscii準拠です。
照合順序と Unicode のサポート - SQL Server | Microsoft Docs
SELECT * FROM (VALUES (ascii('a'),'a'), (ascii('b'),'b'), (ascii('3'),'3'), (ascii(' '),' '), (ascii(' '),' '), (ascii('-'),'-'), (ascii('('),'(')) as t(asciiコード,ソート文字) ORDER BY ソート文字 COLLATE japanese_bin ASC;
実行結果
| asciiコード | ソート文字 |
|---|---|
| 9 | {Tab} |
| 32 | {Space} |
| 40 | ( |
| 45 | - |
| 51 | 3 |
| 97 | a |
| 98 | b |
Japanese_CI_ASの場合
Japanese_CI_AS(Windows照合順序)の場合の結果は以下となります。
SELECT * FROM (VALUES (ascii('a'),'a'), (ascii('b'),'b'), (ascii('3'),'3'), (ascii(' '),' '), (ascii(' '),' '), (ascii('-'),'-'), (ascii('('),'(')) as t(asciiコード,ソート文字) ORDER BY ソート文字 COLLATE japanese_ci_as ASC;
実行結果
Asciiコード順にソートされない
| asciiコード | ソート文字 |
|---|---|
| 32 | {Space} |
| 45 | - |
| 9 | {Tab} |
| 40 | ( |
| 51 | 3 |
| 97 | a |
| 98 | b |
上記の通り、TabとSpaceの大小関係がJapanese_BINではTab < SpaceなのがJapanese_CI_ASではSpace < Tabになるため、 同じ比較クエリであっても結果が変わります。
select case when ' ' COLLATE japanese_bin -- space < ' ' COLLATE japanese_bin -- tab then 'o' else 'x' end /* 結果 'x' */ select case when ' ' COLLATE japanese_ci_as -- space < ' ' COLLATE japanese_ci_as -- tab then 'o' else 'x' end /* 結果 'o' */
ハイフンの例外パターンについて
Windows照合順序の場合、ハイフンは他の記号と挙動が異なります。 文字と文字の間にハイフンが存在する場合、並び替え(大小比較)時のみハイフンが無視されます。
以下の場合、一見すると2文字目の'b'と'-'の比較で結果'x'になるように見えますが、
'b-c'は'bc'と見なされるため、実質'bb'<'bc'の比較となり、結果は'o'となります。
select case when 'bb' COLLATE japanese_ci_as < 'b-c' COLLATE japanese_ci_as then 'o' else 'x' end /* 結果 'o' */ -- 補足 -- ※並び替えや大小比較上でのみ無視されるだけなので以下のようにイコール判定にはならない select case when 'bb' COLLATE japanese_ci_as = 'b-b' COLLATE japanese_ci_as then 'o' else 'x' end /* 結果 'x' */ -- ※バイナリ照合順序の場合はハイフンは考慮される select case when 'bb' COLLATE japanese_bin < 'b-b' COLLATE japanese_bin then 'o' else 'x' end /* 結果 'x' */
参考例
Japanese_BINではハイフンを考慮してソートされますが、Japanese_CI_ASではハイフンを無視してソートされます。
SELECT * FROM (VALUES ('a'), ('c'), ('-'), ('aaa'), ('aca'), ('a-b'), ('a-d')) as t(ハイフン) ORDER BY ハイフン COLLATE japanese_bin ASC;
実行結果
| ハイフン |
|---|
- |
| a |
| a-b |
| a-d |
| aaa |
| aca |
| c |
SELECT * FROM (VALUES ('a'), ('c'), ('-'), ('aaa'), ('aca'), ('a-b'), ('a-d')) as t(ハイフン) ORDER BY ハイフン COLLATE japanese_ci_as ASC;
実行結果
| ハイフン |
|---|
- |
| a |
| aaa |
| a-b |
| aca |
| a-d |
| c |
以上
