T-SQL - TRANSLATE 関数の使い方と REPLACE 関数との違い

TRANSLATE 関数の基本とREPLACE 関数との違い

SQL Server を使って開発をしていると、文字を置き換えたい時がたまにあるかと思います。

T-SQL で文字を置き換えると聞いてまず思いつくのが REPLACE 関数かもしれませんが、TRANSLATE 関数を使っても文字を置き換えることができます。

ですが、REPLACE 関数と TRANSLATE 関数では少し動きが違います。

この記事では、SQL Server の TRANSLATE 関数の基本的な使い方とREPLACE 関数との違いなどをご紹介します。

TRANSLATE 関数の基本的な使い方

SQL Server の TRANSLATE 関数は、文字列の中の特定の 1 文字を、指定した 1 文字に、まとめて置換してくれる関数です。

SQL Server 2017 以降で利用可能です。

TRANSLATE 関数の基本構文は以下の通りです。

TRANSLATE ( inputString, characters, translations )

inputString: 第一引数に置換される文字列を指定します。データ型としては、NVARCHAR、VARCHAR、NCHAR、CHAR 型の文字列が指定できます。

characters: 第二引数に、置換したい文字をつなげた文字列を指定します。こちらも、データ型としては、NVARCHAR、VARCHAR、NCHAR、CHAR 型の文字列が指定できます。

translations: 第三引数に、characters に対応した置換後の文字列を指定します。 データ型は characters と同じにする必要があります。

また、characters と translations の文字列の長さは同じでないとエラーになります。

戻り値は inputString と同じデータ型で、characters を 1 文字 1 文字 translations で指定した文字に置換した文字列が 返ります。

characters と translations は文字列として指定しますが、実際には 1 文字 1 文字がどの文字がどの文字に対応して置き換えられるかという点だけが重要で、文字の出現の順番や、文字がつながっているかなどは関係ありません。

例えば、TRANSLATE (@StringValue, 'abc', '123') のように指定した場合は、次のようなマッピングで置換するということになります。

それでは、実際に TRANSLATE 関数を使って、文字を置き換えてみます。

SELECT  TRANSLATE('abcde', 'abc', '123') AS Result1,
        TRANSLATE('aaaaa', 'abc', '123') AS Result2,
        TRANSLATE('abcdeedcba', 'abc', '123') AS Result3,
        TRANSLATE(NULL, 'abc', '123') AS Result4;

[実行結果]

TRANSLATE('abcde', 'abc', '123') は abc がそれぞれ 123 に置き換えられて、'123de' が返っています。

TRANSLATE('aaaaa', 'abc', '123') は最初の a がそれぞれ 1 に置き換えられて、'11111' が返っています。

TRANSLATE('abcdeedcba', 'abc', '123') も全ての abc がそれぞれ 123 に置き換えられるので、'123deed321' が返っています。

TRANSLATE(NULL, 'abc', '123') では、第一引数の置換される文字列が NULL なので NULL が返っています。

TRANSLATE 関数は、全角を半角に置き換えたい時なんかにも便利に使えます。

DECLARE @StringValue NVARCHAR(100) = N'ＢＣＡ';

SELECT  @StringValue AS StringValue,
        TRANSLATE(@StringValue, N'ＡＢＣ', N'ABC') AS TranslateResult;

[実行結果]

もうひとつ確認しておきたいのは、TRANSLATE 関数による置換は 1 文字ずつ対応した文字に置換されますが、処理はまとめて行われているという点です。

前から順番に処理をしていっている訳ではないので、次のクエリーのように a が b に置換されるようになっていても、元々 a から置換された b が、b から 2 に置換されることはありません。

SELECT TRANSLATE('abcde', 'abc', 'b23') AS Result;

[実行結果]

TRANSLATE('abcde', 'abc', 'b23') の戻り値は 'b23de' となっていますね。

TRANSLATE 関数と REPLACE 関数の違い

REPLACE 関数も文字列を置換する関数で構文は次の通りです。

REPLACE ( string_expression , string_pattern , string_replacement )

string_expression には処理対象の文字列を、string_pattern には置換する元の部分文字列を、string_replacement には string_pattern を置き換えた後の文字列を指定します。

TRANSLATE 関数と REPLACE関数の違いには次のようなものがあります。

TRANSLATE 関数は一文字ずつ対象の文字が見つかったら置換しますが、REPLACE 関数は部分文字列が連続して出てきた時のみ置換します。

試しに 'abcde_edcba_abcde' という文字列に対して、TRANSLATE 関数と REPLACE関数を使って、'abc' を '123' 置き換えてみます。

DECLARE @StringValue NVARCHAR(100) = 'abcde_edcba_abcde';

SELECT  TRANSLATE(@StringValue, 'abc', '123') AS TranslateResult,
REPLACE(@StringValue, 'abc', '123') AS ReplaceResult;

[実行結果]

TRANSLATE 関数の戻り値は、'123de_ed321_123de' となっており、abc だけでなく、cba も対応する文字に置き換えられています。

一方、REPLACE 関数の戻り値は、'123de_edcba_123de' となっており、abc と連続している箇所のみ 123 に置き換えられています。

また、TRANSLATE 関数では、第 2 引数の置換する文字列と第 3 引数の置換後の文字列の文字数が同じでなければエラーになります。

DECLARE @StringValue NVARCHAR(100) = 'abcde_edcba_abcde';

SELECT TRANSLATE(@StringValue, 'abc', '') AS TranslateResult;

[実行結果]

ですが、REPLACE 関数のほうは、第 2 引数の置換する文字列と第 3 引数の置換後の文字列の文字数が同じでなくて大丈夫です。

ですので、REPLACE 関数は第 3 引数に空文字を指定して、部分文字列を削除しまったり、単語を変換するような用途にも使えます。

DECLARE @StringValue NVARCHAR(100) = 'abcde_edcba_abcde';

SELECT REPLACE(@StringValue, 'abc', '') AS ReplaceResult;

[実行結果]

戻り値は 'de_edcba_de' となっており、'abc' が削除されています。

REPLACE関数を使って、TRANSLATE 関数と同じようなことをしようと思うと、REPLACE関数を文字数分ネストして、1 文字ずつ置換する方法が考えられます。

ですが、REPLACE関数の場合、置換の優先順位がでてきてしまいます。

つまり、最初に置き換えられた後の文字が、次の置換の置換前の文字になっていた場合、次の置換でまたその文字が置き換えられてしまいます。

DECLARE @StringValue VARCHAR(100) = 'abcde_edcba';

SELECT  TRANSLATE(@StringValue, 'abc', '123') AS TranslateResult1,
REPLACE(REPLACE(REPLACE(@StringValue, 'a', '1'), 'b', '2'), 'c', '3') AS ReplaceResult1,
TRANSLATE(@StringValue, 'abc', 'b23') AS TranslateResult12,
REPLACE(REPLACE(REPLACE(@StringValue, 'a', 'b'), 'b', '2'), 'c', '3') AS ReplaceResult2;

[実行結果]

'abc' に対して '123' に置換したい時には、TRANSLATE 関数とネストした REPLACE関数

'abc' に対して 'b23' に置換したい時すると、TRANSLATE 関数の戻り値は 'b23de_ed32b'、REPLACE関数

TRANSLATE 関数とREPLACE関数、どちらも便利な状況があると思いますので、必要に応じて使いわけてくださいね。

以上、SQL Server の TRANSLATE 関数の基本的な使い方とREPLACE 関数との違いなどをご紹介しました。