SQLで値の置換をしたい！【CASEとREPLACEのちがい】

SQLで値の置換ができる

　先日、SQLで値の置換を行う機会があり、そもそもSQLで値を変換することができることに驚いたのと、REPLACEとCASEの挙動の違いについて知ることができたので、まとめようと思います。

REPLACEとCASEの違い

例えば以下のように、codeカラムに数値が入ったproductテーブルがあるとします。

code
1
2
3
4
12
123

SELECT
    CASE
        WHEN code = 1 THEN 11
        WHEN code = 2 THEN 22
        WHEN code = 3 THEN 33
        ELSE code
    END
    AS replaced_code,
FROM product

SELECT
    REPLACE (code, '1', '11'),
    REPLACE (code, '2', '22'),
    REPLACE (code, '3', '33')
FROM product  

一見どちらも同じ結果が得られそうに見えますが、CASEとREPLACEでは、挙動が違います。
この2つのSQLには、下記の違いがあります。

1. 部分一致か完全一致か
2. 1つの結果列を返すか複数の結果列を返すか
3. 値そのまま(今回は数値型)を扱うか文字列に変換して扱うか

CASEの場合

code列の値そのものを条件として判定します

• codeが1の場合 → 11を返す
• codeが2の場合 → 22を返す
• codeが3の場合 → 33を返す
• それ以外の場合 → 元の値がそのまま返される

また、CASE式は条件に当てはまった場合、それ以降の条件は実行されません。
そのため1つの結果列のみ返します。

code	replaced_code
1	11
2	22
3	33
4	4
12	12
123	123

このようにCASEの場合は、
値が条件に完全一致した場合は置換後の値を、それ以外は元の値を返し、
条件の数に関わらず1つの値に対して1つの結果が返ってきます。

REPLACEの場合

まず注意すべきこととして、REPLACEは文字列しか扱うことができません。
今回の例のように扱いたいカラムが数値の場合は文字列に変換する必要があります。
使用するRDBMSごとの方法で型変換してください。
（MySQLの場合は暗黙の型変換を行ってくれるので例に挙げたSQLのままでも動くらしいです）

code列を文字列に変換し、値に含まれる各文字に対して条件と比較します。

• 1つ目の列：codeの文字「1」を「11」に置換
• 2つ目の列：codeの文字「2」を「22」に置換
• 3つ目の列：codeの文字「3」を「33」に置換
• 5つ目の列：codeの文字「12」のうち「1」を「11」に置換→「112」、「2」を「22」に置換→「122」

REPLACEの場合、1つ目の条件に当てはまっても、次の条件が実行されます。
そのため条件の数だけ、今回でいえば3つの結果列が返ります。

code	REPLACE(code,1,11)	REPLACE(code,2,22)	REPLACE(code,3,33)
1	11	1	1
2	2	22	2
3	3	3	33
4	4	4	4
12	1112	122	12
123	11123	1223	1233

このようにREPLACEの場合は、
各値の文字列の中に、条件に部分一致するものがあれば置換が実行され、
1つの値に対して、条件の数だけ結果が返ってきます。

まとめ

今までSQLは値を取得するだけのものと勘違いしていましたが、文字列の置換などの値の変換もできることがわかりました。条件分岐などと組み合わせるともっといろいろなことができそうです！

参考文献