【SQL】照合順序が日本語以外のときにバイト数でSUBSTRINGする

SQL ServerのSUBSTRINGはSUBSTRING ( expression ,start , length ) のように使用でき、仕様では、ntext、char、varcharのときはlength=文字数で、text、image、binary、varbinaryのときは、length=バイト数を指定できる。
英語のOSにSQLサーバを入れると、デフォルトでは、LATINが照合順序（Collation・コレーション）で設定され、日本語を保存するには、nvarcharなど”n”がつくnchar、nvracharをデータ型として使用する必要がある。
このときに、バイト数でSUBSTRINGを行おうとすると、nvarcharをtextにconvertし、その上でSUBSTRINGを行うが、nvarcharをtextにconvertすると、日本語は??????のように文字化けてしまう。

そこで、回りくどいが、テンプテーブルを日本語の照合順序で作成し、テンプテーブルにデータに対しSUBSTRINGを行うことで、バイトによるSUBSTRING結果を取得することができる。

declare @jpstring nvarchar(10);

set @jpstring = N'あ23えお';

CREATE TABLE #TMP_TABLE (JP_COLLATION varchar(10) COLLATE Japanese_CI_AS)

insert into #TMP_TABLE values(@jpstring);

select LEN(JP_COLLATION) as 文字の長さ,
DATALENGTH(JP_COLLATION) as 文字のバイト長, 
substring(JP_COLLATION,1,4) as そのままSUBSTRING4,
substring(CONVERT(text, JP_COLLATION),1,1) as Textに変換後SUBSTRING1,
substring(CONVERT(text, JP_COLLATION),1,2) as Textに変換後SUBSTRING2,
substring(CONVERT(text, JP_COLLATION),1,3) as Textに変換後SUBSTRING3,
substring(CONVERT(text, JP_COLLATION),1,4) as Textに変換後SUBSTRING4,
substring(CONVERT(text, @jpstring),1,4) as もとの文字列をTextに変換後SUBSTRING4,
substring(CONVERT(ntext, @jpstring),1,4) as もとの文字列をNTextに変換後SUBSTRING4
from #TMP_TABLE;

Share this: