SQL Server の LEN 関数は文字列の長さを正確に取得出来ない・・・だけじゃない
サロゲート文字*1を含む文字列だと、LEN 関数正しい文字列長を取得出来ない。
-- このSQLを実行すると、1ではなく2が返される
SELECT LEN(N'𠮷');
注意：環境によっては□が表示されるかもしれないけど、実際はつちよし (吉の上が士じゃなくて土) です。
以下、□が現れたら脳内変換するか、表示できる環境で見るかしてくだしあ。
で、実は問題は LEN 関数だけじゃなくて、他の文字列関数や、文字列型にまで及ぶ。
文字列型
例えば、nchar(1) 型や nvarchar(1) の列には「𠮷」を格納することは出来ないし*2、nchar(1) 型や nvarchar(1) 型の変数に代入すると、後ろ 1 バイト分が切り詰められ、結果文字化けする。
だから例えば、「10 文字あれば十分」と判断したとしても、安直に nvarchar(10) なんてしてはいけないということ。
もし全部サロゲート文字で埋められたとしたら、5 文字しか入らないので、「サロゲート文字は非対応」とするか*3、nvarchar(20) と倍の指定をしておく必要がある。
nchar の場合、指定した長さの 2 倍のバイト分必ず領域を占有するので、サロゲート文字が絶対に入らない保証がないなら、常に nvarchar を使っておくのが望ましい。
文字列関数
冒頭で紹介した LEN をはじめ、文字列関数はほぼ全滅状態。
以下は、よく使うであろう関数に非サロゲート文字とサロゲート文字を渡した際の結果のまとめ。
部分文字列
サロゲート文字に対して LEFT と RIGHT の結果を連結すると、一文字になるというのは面白い。
-- 吉
SELECT SUBSTRING(N'吉', 1, 1);
-- 文字化け
SELECT SUBSTRING(N'𠮷', 1, 1);-- 吉吉
SELECT LEFT(N'吉', 1) + RIGHT(N'吉', 1);
-- 𠮷
SELECT LEFT(N'𠮷', 1) + RIGHT(N'𠮷', 1);
-- 文字化け 文字化け
SELECT LEFT(N'𠮷', 1), RIGHT(N'𠮷', 1);-- 55362 57271
SELECT UNICODE(LEFT(N'𠮷', 1)), UNICODE(RIGHT(N'𠮷', 1));
検索
-- 2
SELECT CHARINDEX(N'hoge', N'吉hoge');
-- 3
SELECT CHARINDEX(N'hoge', N'𠮷hoge');-- 2
SELECT PATINDEX(N'%h_ge%', N'吉hoge');
-- 3
SELECT PATINDEX(N'%h_ge%', N'𠮷hoge');-- 0
SELECT PATINDEX(N'h_ge', N'吉hoge');
-- 3・・・これはバグだろ
SELECT PATINDEX(N'h_ge', N'𠮷hoge');
ちなみに使ったことないからここには挙げてないけど、STUFF は検索位置で削除・挿入を行うためもちろんアウト。
大文字化・小文字化
大文字化・小文字化は、「𠮷」に関しては何も作用しないためか正常に動作する*4が、他のサロゲート文字でも動作するかはわからない。
-- 吉
SELECT UPPER(N'吉');
-- 𠮷
SELECT UPPER(N'𠮷');-- 吉
SELECT LOWER(N'吉');
-- 𠮷
SELECT LOWER(N'𠮷');
反転
-- 吉
SELECT REVERSE(N'吉');
-- 文字化け
SELECT REVERSE(N'𠮷');
置換
置換は内部で CHARINDEX とか LEN 相当の処理を行っているんだろうけど、どっちもうまく動かないことが幸いして置換自体に問題はなさそう。
-- 吉
SELECT REPLACE(N'吉hoge', N'hoge', N'');
-- 𠮷
SELECT REPLACE(N'𠮷hoge', N'hoge', N'');-- hoge
SELECT REPLACE(N'吉hoge', N'吉', N'');
-- hoge
SELECT REPLACE(N'𠮷hoge', N'𠮷', N'');-- 吉吉
SELECT REPLACE(N'吉hoge', N'hoge', N'吉');
-- 𠮷𠮷
SELECT REPLACE(N'𠮷hoge', N'hoge', N'𠮷');
繰り返し
繰り返しも対象文字列をどうこうするようなものじゃないからきちんと動く。
-- 吉吉
SELECT REPLICATE(N'吉', 2);
-- 𠮷𠮷
SELECT REPLICATE(N'𠮷', 2);
どうすればいいのか
ユーザ定義関数でも組む？
でもこれだけの数のユーザ定義関数を組むのは骨だし、がんばって組んだところで速度は・・・だし。
結局、文字列操作は極力 SQL 側でやらないほうがいいということかな。
