知っておくと便利な関数 - STUFF

ここでは、知っておくと便利なビルトイン関数のひとつ、STUFF 関数についてご説明します。

STUFF 関数

STUFF 関数は、文字列の指定した箇所を、指定した文字列に置き換えてくれる関数です。

入力引数は 4 つで、構文は以下の通りです。

STUFF([文字列], [開始位置], [長さ], [置き換える文字列])

[文字列] の [開始位置] から、指定した [長さ] の文字数の文字を削除して、その位置に [置き換える文字列] を挿入した文字列を返します。

[開始位置] は、最初の文字を 1 として数え、削除の開始位置を整数で指定します。

[開始位置] に 0 以下の値を指定したり、[文字列] の長さより大きい値を指定すると、戻り値は NULL になります。

[長さ] には、削除する文字数を整数で指定します。

[長さ] にマイナスの値を指定すると、戻り値は NULL になります。

[長さ] に 0 を指定すると、削除は行われずに、[開始位置] に [置き換える文字列] が挿入されます。

[開始位置] + [長さ] が [文字列] の長さより大きくなる場合は、[開始位置] から [文字列] の最後までの文字が削除されて、[置き換える文字列] が挿入されます。

また、同じ方法で、文字列の代わりにバイナリデータを置き換えることもできます。

それでは、STUFF 関数を使って文字列を置き換えてみましょう。

DECLARE @Value VARCHAR(10) = 'ABCDEFGHIJ';

SELECT	 STUFF(@Value, 3, 5, 'XXX') AS Value1,
         STUFF(@Value, 3, 0, 'XXX') AS Value2,
         STUFF(@Value, 3, 10, 'XXX') AS Value3,
         STUFF(@Value, 0, 5, 'XXX') AS Value4;

Value1 は、'ABCDEFGHIJ' の 3 文字目から 5 文字の 'CDEFG' が削除されて、そこに XXX が挿入され 'ABXXXHIJ' が返っています。

Value2 は、[長さ] が 0 なので、'ABCDEFGHIJ' の 3 文字目から XXX が挿入され、'ABXXXCDEFGHIJ' が返っています。

Value3 は、'ABCDEFGHIJ' の 3 文字目以降の文字が全て削除され、そこに XXX が挿入されて 'ABXXX' になっています。

Value4 は、[開始位置] が 0 なので、戻り値が NULL になっています。

「カラムの値からカンマ区切り (CSV) の文字列を生成する」でご紹介したように、クエリーで FOR XML 句などを使って文字列をつなげて、カンマ区切りの文字列を生成した後に、1 文字目のカンマを取り除きたいような時にも STUFF 関数が使えます。

DECLARE @Value VARCHAR(100) = ',Tokyo,Osaka,Fukuoka';

SELECT STUFF(@Value, 1, 1, '') AS Value1;

以上、知っておくと便利なビルトイン関数のひとつ、STUFF 関数についてご説明しました。