T-SQL: ケースセンシティブ（大文字と小文字を区別して）で文字列を比較する

SQL Server と文字と小文字の区別

SQL Server のデフォルトコレーション (照合順序) のまま使っている場合、文字と小文字を区別しないコレーションになっていると思います。

例えば、'AbcDE' = 'abcde' は TRUE と判断されます。 外部キー制約のチェックなどでも同じとみなされ、大文字小文字が違っても保存できます。

クライアントプログラムがケースセンシティブな場合は少し注意が必要な点でもあります。


たまに、データの分析などでインポートしたデータなどと、一時的にケースセンシティブで文字列を比較したいような時はないでしょうか。

今回はそんな時に使えるクエリーをご紹介します。

コレーション (照合順序)を指定して文字列を比較する

次のような Groups テーブルがあるとします。

ケースセンシティブ（大文字と小文字を区別して）で文字列を比較する 1
ケースセンシティブ（大文字と小文字を区別して）で文字列を比較する 2


Groups　テーブルのデータは少し問題があり、インポートした Groups_Temp というテーブルに正しい GroupCode と GroupName が入っているとします。

ケースセンシティブ（大文字と小文字を区別して）で文字列を比較する 3
ケースセンシティブ（大文字と小文字を区別して）で文字列を比較する 4


そこで、Groups_Temp テーブルの GroupCode と GroupName と大文字小文字も区別して合致しない Groups テーブルの値を取得して確認したいとします。

Groups_Temp の GroupCode と GroupName と合致しない Groups のレコードを取得するクエリーは何通りかありますが、例えば次のように取得することができます。 

SELECT	G.*
FROM	Groups AS G
	   LEFT JOIN Groups_Temp AS T
	      ON G.GroupCode = T.GroupCode
	         AND G.GroupName = T.GroupName
WHERE	T.GroupCode IS NULL;

[ 実行結果 ]
ケースセンシティブ（大文字と小文字を区別して）で文字列を比較する 5

ただ、これでは大文字・小文字が区別されておらず、値が違うレコードしか取得されていません。

次のように比較する箇所に続けてケースセンシティブなコレーション(照合順序)を指定することで、大文字小文字も区別して合致しない値を取得することができます。 

SELECT	G.*
FROM	Groups AS G
	   LEFT JOIN Groups_Temp AS T
	      ON G.GroupCode = T.GroupCode COLLATE SQL_Latin1_General_CP1_CS_AS
	         AND G.GroupName = T.GroupName COLLATE SQL_Latin1_General_CP1_CS_AS
WHERE	T.GroupCode IS NULL;

[ 実行結果 ]
ケースセンシティブ（大文字と小文字を区別して）で文字列を比較する 6

大文字・小文字も区別されて、合致しない Groups テーブルのレコードが取得できていますね。

コレーション (照合順序)の確認方法

何のコレーション (照合順序)を指定するべきか、という点ですが、簡単なのは比較に使っているカラムのコレーションを確認して、_CI_ となっている部分を _CS_ に変更するといいと思います。

CI、CS の一文字目の C は大文字小文字(Case) という意味です。 二文字目の I (Insensitive) は区別なし S (Sensitive) は区別ありです。


カラムのコレーション (照合順序)は次のようなクエリーで確認することができます。 

SELECT	T.name As table_name,
	C.name AS column_name,
	C.collation_name
FROM	sys.tables AS T
	   INNER JOIN sys.columns AS C
	      ON T.object_id = C.object_id
WHERE	T.name = 'Groups' -- テーブル名
	AND C.name IN ('GroupCode','GroupName'); -- カラム名

[ 実行結果 ]
ケースセンシティブ（大文字と小文字を区別して）で文字列を比較する 7

日本語の環境では Japanese_CI_AS などが使われているかもしれませんが、その場合は Japanese_CS_AS にしてみて下さい。


全角文字と半角文字を区別もコレーション(照合順序)で指定可能です。

WS が全角・半角文字を区別するオプションなのですが、ついていない場合は全角・半角文字が区別されていませんので、区別したい時は WS のついたコレーションを指定してください。


また、異なるデータベース間のコレーション(照合順序)が違うカラム同士の文字列を比較する時などに 「 照合順序の競合を解決できません 」 というようなエラーが出るときがあります。

そんな時も今回と同様に、比較の箇所にコレーション(照合順序)を指定することでエラーを回避することができます！

© 2010-2024 SQL Server 入門