sqlserver 文字列 比較 末尾 空白
こんにちは、nagino です。
うちのブログは宣伝無し、会社には秘密、なので、ほぼほぼ検索サイト経由の来訪のみだったりします。
で、タイトルのキーワードでの検索がちょっと目立ったので、以下まとめです。
# ちなみに、「SQLServer」ではなく、「SQL Server」が正式な名称です。
SQL Server で文字列型に対する末尾の空白については、以下のような動作をします。
※ 空白は分かり難いので、以下ではアンダースコア「_」で空白を表記します。
● char 型 / nchar 型
列のサイズになるまで、末尾を空白文字で埋めます。
char(3) に対して 'A_' を入れても、'A__' となります。
● nvarchar 型
末尾の空白文字は、指定したとおりに入ります。
nvarchar(20) に対して 'A_' を入れると、'A_' が入ります。
自動的に切り捨てられたりはしません。
● varchar 型
ANSI_PADDING が ON （デフォルト）の場合は、末尾の空白は指定したとおりに入ります。
varchar(20) に対して 'A_' を入れると、'A_' が入ります。
自動的に切り捨てられたりはしません。
ANSI_PADDING が OFF の場合は、末尾の空白は切捨てられます。
varchar(20) に対して 'A_' を入れると、'A' が入ります。
参考 URL
http://msdn.microsoft.com/ja-jp/library/ms187403.aspx
ですので、可変長文字列である varchar 型や nvarchar 型では、実はデータとしては末尾の空白を保持しているのです。
しかし注意すべき仕様が、比較処理の際の動作にあります。
文字列を比較する際は、末尾の空白を無視して比較処理が行われます。
'A' と 'A_' を比較する場合、'A' と 'A' として比較されます。
参考 URL
http://msdn.microsoft.com/ja-jp/library/ms191529.aspx
ですので、ともすると可変長文字列は格納時に末尾の空白が切り捨てられているように誤解されることも多くあります。
さて、これで終わっても良いのですが、これだと空白の有無も含めて比較したいケースで困ります。
その場合は、比較する前にバイナリ型に変換する必要があります。
CONVERT(varbinary, column-name)
これで比較すれば、空白の文字数も含めて厳密に区別されます。
なお、文字列長を比較に含めれば良いという話もありますが、改行文字や制御文字が含まれているケースを考えると、バイナリ型に変換するほうがお勧めです。
というのも、末尾に空白、改行（CR）、改行（LF）のどれか 1 文字がある場合に、区別がつきません。
例えば、以下のようなケースで長さ 2 のケースの区別がつきません。
SELECT
DATALENGTH('a' + ' '), -- 2
DATALENGTH('a' + char(10)), -- 2
DATALENGTH('a' + char(13)), -- 2
DATALENGTH('a' + char(10) + char(13)), -- 3
LEN('a' + ' '), -- 1
LEN('a' + char(10)), -- 2
LEN('a' + char(13)), -- 2
LEN('a' + char(10) + char(13)) -- 3
まあ、空白を無視しない比較演算子、ないしはオプションのようなものがあれば良いのですけれども、無いものはしょうが無いので、このような手間がかかります。