SQLServerのLEN関数では正確な文字数が取得できない場合

SQLServerの文字列の文字数を取得するLEN関数ですが、正確に文字数を取得できない場合があるのでメモしておきます。

文字列にサロゲートペアを使用する文字が含まれている

文字数を取得する対象の文字列にサロゲートペアを使用する文字が含まれる場合に文字数が正しく取得できません。
具体的には、LEN関数は1文字のサロゲートペアを使用する文字に対して「1」ではなく「2」を返します。

SQLの実行と結果

サロゲートペアを使用する文字の結果は「2」になっています。

LEN関数で正確な結果が取得できないので、当然SUBSTRING関数で部分文字列を取得した結果も予期しないものになります。

SUBSTRINGでもサロゲートペアを使用する文字は2文字として取得する必要があります。

テーブルを設計する際にも注意が必要です。
テーブルに保持するデータとしてもサロゲートペアを使用する文字の文字数は1文字につき2桁となります。
ですのでサロゲートペアを使用する文字が含まれる文字列を扱うシステムやアプリケーションでSQLServerを使用するのであれば、テーブルの列の桁数を2倍にする必要があります。
例えば10桁の文字列を保持する列には「NVARCHAR(10)」ではなく「NVARCHAR(20)」と設定します。（データ型はVARCHARではなくNVARCHARになります。）

ちなみにDATALENGTH関数を使用してサロゲートペアを使用する文字の桁数を取得すると4が返ってきます。

文字列の最後が半角スペースで終わってる

文字数を取得する対象の文字列の最後（末尾）が半角スペースの場合に文字数が正しく取得できません。

SQLの実行と結果

末尾の半角スペースは文字数には含まない結果が返ってきます。

MicrosoftのドキュメントのLEN関数のページには、関数の動作の説明として「指定された文字列式の、末尾の空白を除いた文字数を返します。」と記載されています。
LEN関数が末尾の半角スペースを除いた文字数を返すのは仕様なので、末尾に半角スペースを含む文字列の文字数を半角スペースを含んで取得する際には、LEN関数を使用するのではなく別の方法（プログラムで取得するなど）を検討する必要があります。