【SQL Server】入れ子なしの複数置換【REPLACE】

## 前文
複数の置換は`REPLACE`の入れ子が定石。
関連投稿：
[日本語並び順の考察 - Qiita](https://qiita.com/god19/items/5cb8f5d40706ee746aca#%E8%A7%A3%E6%B1%BA%E7%AD%96 "日本語並び順の考察 - Qiita")

しかし見辛いよね。いかにもその場の思い付きだけで実装しました感があるし。
ぐぐると`CASE`式を利用する方法が出てくる。
[【SQL】REPLACE関数・CASE式 複数の文字列を置換する方法 - buralog](https://buralog.jp/sql-using-replace-function-update-column/ "【SQL】REPLACE関数・CASE式 複数の文字列を置換する方法 - buralog")

これはこれでどうだろう。
`CASE`式で分岐するから、一つの対象について複数の置換は行われないよね。これでは`REPLACE`の入れ子と動作が異なる。

入れ子ではない、もっとスマートな実装方法はないものか。曲がりなりにもDB系の言語だから、置換定義は配列（データセット）として独立しているような方向性で考えてみた。

## 環境

| SQLServer | SSMS |
| ---- | ---- |
| 2017 | v18.1 |

※ 恐らくバージョンには依存しないと思うけど

## 実装例 1
まずは置換対象がスカラ値（変数）の場合。

```SQL:テーブル型変数に置換定義を持たせる実装例
-- 置換定義
DECLARE @REPLACE_DEF TABLE(
      SEQ   INT
    , SRC   NVARCHAR(MAX)
    , DST   NVARCHAR(MAX)
);
INSERT INTO @REPLACE_DEF VALUES
      (1, '零', '0')
    , (2, '壱', '1')
    , (3, '弐', '2')
    , (4, '参', '3')
;

-- 置換対象
DECLARE @STR    NVARCHAR(MAX) = '零壱弐参';

-- 置換前
SELECT @STR;

-- 置換実行
SELECT
    @STR = REPLACE(@STR, SRC, DST)
FROM @REPLACE_DEF
ORDER BY SEQ
;

-- 置換後
SELECT @STR;
```

```SQL:実行結果(置換前)
(列名なし)
零壱弐参
```

```SQL:実行結果(置換後)
(列名なし)
0123
```

まずこの方法の面白い点は`SELECT`文で変数に代入している点。
通常、この手の実装は単一レコードを`SELECT`してスカラ値として変数に代入するケースが多いと思うけど、複数レコードの場合はそれ相応にグルグル回って代入される。
応用例として[カンマ区切り連結](https://qiita.com/god19/items/3822f5ea4a5bef8bd932#%E5%AE%9F%E8%A3%85-4 "【神の】クエリでフィボナッチ数列【数列】 - Qiita")なんてこともできるよね。

それと重要なことは`データ`と`アルゴリズム`の独立性。
[MVC](https://ja.wikipedia.org/wiki/Model_View_Controller "Model View Controller - Wikipedia")のようなマクロ的な設計方針の重要性はよく語られるけど、局所的な実装方針、例えば上記のような`データ`と`アルゴリズム`の独立性は軽視されがちというか、無頓着な人が多いよな。

`データ`と`アルゴリズム`が分離していれば、まず可読性が高くなる。
~~みんな大好きコピペ~~移植性も高くなる。データ部を差し替えるだけで済むからね。
関数化したければ置換定義変数と置換対象変数を引数に、アルゴリズムを関数内に書けば良いだけ。
置換パターンのメンテナンス性、独立性をより高めたければ置換パターンを実テーブル（マスタテーブル）に持たせれば良い。

逆に置換定義はその場で使い捨て、無名関数的な実装なら以下。コードも少しシンプルになる。

```SQL:FROM句に置換定義を持たせる実装例
-- 置換対象文字列
DECLARE @STR    NVARCHAR(MAX) = '零壱弐参';

-- 置換前
SELECT @STR;

-- 置換実行
SELECT
    @STR = REPLACE(@STR, SRC, DST)
FROM (VALUES
      (1, '零', '0')
    , (2, '壱', '1')
    , (3, '弐', '2')
    , (4, '参', '3')
)REPLACE_DEF(SEQ, SRC, DST)
ORDER BY SEQ
;

-- 置換後
SELECT @STR;
```

```SQL:実行結果(置換前)
(列名なし)
零壱弐参
```

```SQL:実行結果(置換後)
(列名なし)
0123
```

## 実装例 2
お次は置換対象がデータセット（レコード）の場合。

```SQL:置換対象がデータセット
-- 置換定義
DECLARE	@REPLACE_DEF TABLE(
	  SEQ	INT
	, SRC	NVARCHAR(MAX)
	, DST	NVARCHAR(MAX)
);
INSERT INTO @REPLACE_DEF VALUES
	  (1, '零', '0')
	, (2, '壱', '1')
	, (3, '弐', '2')
	, (4, '参', '3')
	, (5, '', '')		-- 終端
;

-- 置換対象
DECLARE @STR TABLE(
	  STR	NVARCHAR(10)
);
INSERT INTO @STR VALUES
	  ('零')
	, ('零壱')
	, ('零壱弐')
	, ('零壱弐参')
;

-- 置換前
SELECT STR FROM @STR;

-- 置換実行
;WITH CTE_REPLACE AS (
	SELECT
		  def.SEQ
		, REPLACE(str.STR, def.SRC, def.DST)	STR
		, def.SRC
	FROM @STR				str
	INNER JOIN @REPLACE_DEF	def
		ON	def.SEQ = 1
	UNION ALL
	SELECT
		  def.SEQ
		, REPLACE(str.STR, def.SRC, def.DST)
		, def.SRC
	FROM CTE_REPLACE		str
	INNER JOIN @REPLACE_DEF	def
		ON	def.SEQ = str.SEQ + 1
)
SELECT STR FROM CTE_REPLACE WHERE SRC = '' ORDER BY STR
;
```

```SQL:実行結果(置換前)
STR
零
零壱
零壱弐
零壱弐参
```

```SQL:実行結果(置換実行)
STR
0
01
012
0123
```

共通テーブル式は応用例が多いよね。実に様々なことができる。今回もまたその一例。
置換対象の全レコードに対し、共通テーブル式で置換定義レコードを総当たりで内部結合し、置換処理を行っている。
この実装の懸念点を強いて挙げるとすれば、置換対象レコードと置換定義レコードの二重ループである点。二重ループは遅いアルゴリズムの筆頭。両者の数が増大すれば、処理速度は指数関数的に遅くなる。
しかし現実問題として、置換対象レコードが膨大なケースはあっても、置換定義が膨大になることは考え難いかな。それこそ、Google の検索エンジンのような、かなり大がかりなシステムでもない限りはあり得ないと思われ。
百歩譲って、そうしたケースがあったとしても、その場合は実テーブルに格納しての実装になるだろうし、内部結合列に PK なりインデックスを張れば大丈夫でしょう。

## 余談
そもそも入れ子になってしまうのは関数型言語の欠点。俗に言うオブジェクト指向言語ならメソッドチェーンで実装するだろうし可読性も高くなる。

今回自分がぐぐった限りでは、入れ子なしの複数置換を解説したサイトは見つからなかった。
しかし、関数型言語だからといって入れ子が避けられないかといえば違う。ちょっと考えてみれば入れ子にならない実装方法はある訳で。もっと頭を使おうぜ。