SQLで文字列を置換する方法
SQLでデータの操作を行う際に、文字列を置換して取得したい場合もあるでしょう。
この記事では、SQLで文字列を置換する方法について解説します。
文字列の置換を行うには、DBMSに用意されている関数を使うと便利です。
ここでは、文字列を置き換える2つの関数を紹介します。
- REPLACE関数
- TRANSLATE関数
上記の関数は、それぞれ次のDBMSで使用可能です。
- MySQL：REPLACE関数
- PostgreSQL：REPLACE関数・TRANSLATE関数
- Oracle：REPLACE関数・TRANSLATE関数
- SQL Server：REPLACE関数・TRANSLATE
それぞれの使い方を見ていきましょう。
REPLACE関数の使い方
REPLACE関数は、対象の文字列の中から、単語単位で文字列を別の文字列に置き換える関数です。
次のように記述します。
SELECT REPLACE('対象の文字列', '置換前の文字列', '置換後の文字列');
では実際に、SQL文を動かして確認してみます。
次のように記述しましょう。
SELECT REPLACE('potepan_style', 'potepan', 'sql');
上記のSQL文は、「potepan」と書かれている文字列を「sql」に置換します。
実行すると、次のように結果を取得できました。
mysql> SELECT REPLACE('potepan_style', 'potepan', 'sql');
+--------------------------------------------+
| REPLACE('potepan_style', 'potepan', 'sql') |
+--------------------------------------------+
| sql_style |
+--------------------------------------------+
1 row in set (0.00 sec)
ちゃんと指定した文字列が置換されていますね。
置換する文字列は、大文字・小文字を区別するため、完全に一致する文字列の指定が必要です。
次のようにSQL文を実行しても、文字列は置換されません。
mysql> SELECT REPLACE('potepan_style', 'POTEPAN', 'sql');
+--------------------------------------------+
| REPLACE('potepan_style', 'POTEPAN', 'sql') |
+--------------------------------------------+
| potepan_style |
+--------------------------------------------+
1 row in set (0.00 sec)
大文字・小文字の区別だけ注意して使いましょう！
TRANSLATE関数の使い方
TRANSLATE関数は、文字列を別の文字列に置換する関数です。
使う場合は、次のように記述できます。
SELECT TRANSLATE('対象の文字列', '置換前の文字列', '置換後の文字列');
先述した通り、TRANSLATE関数はMySQLでは使用できません。
REPLACE関数と異なるのは、文字列が単語単位で一致しているかどうかです。
REPLACE関数は、第二引数に指定した文字列を単語単位で検索します。
そのため、対象の文字列の中に、置換前の文字列が一致しない場合は、文字列が置き換わりません。
一方、TRANSLATE関数は、第二引数の文字列の先頭から1文字ずつ検索し、位置が対応している第三引数へ置換します。
では、TRANSLATE関数も実行して確認してみましょう。
mydb=# SELECT TRANSLATE('potepan_style', 'potepan', 'sql');
translate
-----------
sqls_slyl
(1 row)
このように、TRANSLATE関数は、REPLACE関数と挙動が異なりますよね。
TRANSLATE関数では、「p」が「s」に、「o」が「q」に、「t」が「l」に、置き換わるよう指定しています。
そのため、上記のような結果になりました。
単語単位で文字列を置き換えたい場合は、REPLACE関数が良いと言えます。
TRANSLATE関数は、1文字単位で置き換えたい場合に有効です。
mydb=# SELECT TRANSLATE('potepan_style', 'oa', 'ao');
translate
---------------
patepon_style
(1 row)
置換する際の正規表現について
前章で、文字列を置換する関数を紹介しました。
ここでは、文字列を置換する際に使える正規表現について解説します。
正規表現は、特定の文字列パターンを、決められた記号を使って文字列で表記するための方法です。
正規表現には、次のようなものがあります。
| 正規表現 | 意味 |
|---|---|
| . | 任意の1文字に一致するもの |
| […] | カッコ内のどれかの文字に一致するもの [abc] ： a b c のどれかに一致するもの [a-z] ： a から z のどれかに一致するもの [^a-z] ： a から z 以外に一致するもの |
| * | 直前の文字の 0回以上の繰り返しに一致するもの |
| + | 直前の文字の 1回以上の繰り返しに一致するもの |
| ? | 直前の文字の 0回または 1回に一致するもの |
| 〇〇|△△|xx | 〇〇 または △△ または xx の文字列に一致するもの |
| ^ | 文字列の先頭に一致するもの |
| $ | 文字列の末尾に一致 |
| {n,m} | 直前のパターンの n回以上 m回以下の連続に一致 |
これら正規表現を使って、文字列の置換を行ってみましょう。
確認するために、次のテーブルを用意しました。
mysql> select * from sample_table; +---------+-----------+------------+ | user_id | user_name | memo | +---------+-----------+------------+ | 1 | 田中 | Aランク | | 2 | 鈴木 | Bランク | | 3 | 佐藤 | Cランク | | 4 | 大野 | Bランク | | 5 | 伊藤 | Aランク | | 6 | 佐々木 | Bランク | +---------+-----------+------------+ 6 rows in set (0.00 sec)
このテーブルの memoカラム の文字列を、正規表現で置換してみます。
例えば、「A、B、C」の文字列を「no」に置換してみましょう。
次のように記述できます。
SELECT user_id, user_name, REGEXP_REPLACE(memo,'[A-Z]', 'no') AS memo FROM sample_table;
正規表現を使って置換する場合は、REGEXP_REPLACE関数を使います。
第二引数に [A-Z] を指定することで、A から Z のどれかに一致する文字列が置換されます。
上記SQL文を実行すると、次の結果を取得できました。
mysql> SELECT user_id, user_name, REGEXP_REPLACE(memo,'[A-Z]', 'no') AS memo FROM sample_table; +---------+-----------+-------------+ | user_id | user_name | memo | +---------+-----------+-------------+ | 1 | 田中 | noランク | | 2 | 鈴木 | noランク | | 3 | 佐藤 | noランク | | 4 | 大野 | noランク | | 5 | 伊藤 | noランク | | 6 | 佐々木 | noランク | +---------+-----------+-------------+ 6 rows in set (0.01 sec)
このように、A〜C の文字列が置換されました！
正規表現を使って置換すると、効率的に文字列を変更可能です。
正規表現については、他の記事でも詳しく解説しているので、ぜひこの機会に一度学んでみてはどうでしょうか。
【関連記事】
▶︎改行を表す正規表現とは？OS別に改行を基本から解説します！
置換を複数同時に行う方法
ここまで、文字列を置換する方法を解説しました。
中には、文字列の置換を複数同時に行いたい場合もあるでしょう。
文字列の置換を複数同時に行うには、REPLACE関数を入れ子で使うことで可能です。
記述方法は、次の通りです。
SELECT REPLACE(REPLACE('対象の文字列', '置換前の文字列', '置換後の文字列'), '置換前の文字列', '置換後の文字列');
置換したい文字列の数だけ、REPLACE関数を入れ子にします。
mysql> SELECT REPLACE(REPLACE('potepan_style', 'potepan', 'POTEPAN'), 'style', 'STYLE') AS name;
+---------------+
| name |
+---------------+
| POTEPAN_STYLE |
+---------------+
1 row in set (0.00 sec)
可読性には欠けますが、期待した通りの結果が取得できます。
複数の文字列を置換する場合は、使ってみてください！
置換する際に条件を設定する
この章では、条件にあわせて返す結果の文字列を置換する方法について見ていきましょう。
条件にあわせて返す結果の文字列を置換するには、CASE式を使います。
CASE式は、SQLに限らずさまざまな言語で使われる構文です。
「条件がAに当てはまればAルート、Bに当てはまればBルートを実行する」といった、条件分岐ができます。
では実際に、条件にあわせて返す結果を置換してみましょう。
次の社員情報を格納したテーブルを用意しました。
mysql> SELECT * FROM employee; +---------------+---------------+----------+ | employee_code | employee_name | division | +---------------+---------------+----------+ | 1 | Suzuki | 1 | | 2 | Hashimoto | 3 | | 3 | Taguchi | 1 | | 4 | Watanabe | 2 | | 5 | Satou | 0 | +---------------+---------------+----------+ 5 rows in set (0.00 sec)
このテーブルの divisionカラム の値を、文字列に置換して取得してみましょう。
次のように記述できます。
SELECT employee_code, employee_name, (CASE division WHEN 1 THEN '営業部' WHEN 2 THEN '開発部' WHEN 3 THEN '販促部' ELSE '不明' END) AS division FROM employee;
上記SQL文の、CASE式の部分だけを取り出して見ていきましょう。
CASE division WHEN 1 THEN '営業部' WHEN 2 THEN '開発部' WHEN 3 THEN '販促部' ELSE '不明' END
まず、CASEの後ろに対象のカラム名（ここでは divisionカラム）を記述します。
その後ろに「WHEN」を記述し、カラムの値を場合ごとに分けていきましょう。
ここでは、divisionカラムの値が「1」の場合は「営業部」、「2」の場合は「開発部」といったように指定しています。
そして「ELSE」の部分は、値がどれにも当てはまらない場合に、置き換える文字列を指定しました。
ここでは「不明」としています。
CASE式の最後に、「END」と記述すればOKです。
上記のSQL文を実行すると、次のように結果を取得できます。
mysql> SELECT employee_code, employee_name, (CASE division WHEN 1 THEN '営業部' WHEN 2 THEN '開発部' WHEN 3 THEN '販促部' ELSE '不明' END) AS division FROM employee; +---------------+---------------+-----------+ | employee_code | employee_name | division | +---------------+---------------+-----------+ | 1 | Suzuki | 営業部 | | 2 | Hashimoto | 販促部 | | 3 | Taguchi | 営業部 | | 4 | Watanabe | 開発部 | | 5 | Satou | 不明 | +---------------+---------------+-----------+ 5 rows in set (0.00 sec)
それぞれの値が、置き換えた文字列になっているのがわかりますね！
なお、CASE式の詳しい使い方は、以下の記事で解説しているので、ぜひ参考にしてみてください。
【関連記事】
▶︎【SQL】複雑な条件にも対応できるCASE式について解説。
置換した上でUPDATEするには？
ここでは、データをアップロードする際に、値を置換する方法を紹介します。
文字列を置換した上でアップデートする際は、REPLACE関数を使って次のように記述しましょう。
UPDATE テーブル名SET カラム名 = REPLACE(カラム名, '置換前の文字列', '置換後の文字列');
REPLACE関数の使い方は、先述した通りです。
実際に、データをアップデートしてみましょう。
UPDATE employee SET employee_name = REPLACE(employee_name, 'Satou', 'Abe');
上記SQL文を実行後、テーブルを確認すると次のように結果を取得できました。
mysql> SELECT * FROM employee; +---------------+---------------+----------+ | employee_code | employee_name | division | +---------------+---------------+----------+ | 1 | Suzuki | 1 | | 2 | Hashimoto | 3 | | 3 | Taguchi | 1 | | 4 | Watanabe | 2 | | 5 | Abe | 0 | | 6 | NULL | 2 | +---------------+---------------+----------+ 6 rows in set (0.00 sec)
ちゃんと、文字列が置換されてアップデートされています！
NULL値を置き換えるには
カラムによっては、値に「NULL」が含まれている場合もあるでしょう。
NULL値を、別の値に一括で置換できたら便利ですよね。
ここでは、DBMSごとにNULL値を置換する方法を見ていきましょう！
MySQLの場合
MySQLでNULL値を置換する場合は、「IFNULL関数」か「COALESCE関数」を使いましょう。
次のように記述します。
SELECT IFNULL(カラム名, '置き換える文字列') FROM テーブル名; SELECT COALESCE(カラム名, '置き換える文字列') FROM テーブル名;
どちらも記述方法は同じです。
では、先ほど作成したテーブルに、NULL値の入ったデータを追加しましょう。
mysql> SELECT * FROM employee; +---------------+---------------+----------+ | employee_code | employee_name | division | +---------------+---------------+----------+ | 1 | Suzuki | 1 | | 2 | Hashimoto | 3 | | 3 | Taguchi | 1 | | 4 | Watanabe | 2 | | 5 | Satou | 0 | | 6 | NULL | 2 | +---------------+---------------+----------+ 6 rows in set (0.00 sec)
このテーブルの employee_nameカラム のNULL値を、別の文字列に置換して取得します。
SQL文の記述は次の通りです。
SELECT IFNULL(employee_name,'Morita') FROM employee;
上記SQL文を実行すると、次のように結果を取得できました。
mysql> SELECT IFNULL(employee_name,'Morita') FROM employee; +--------------------------------+ | IFNULL(employee_name,'Morita') | +--------------------------------+ | Suzuki | | Hashimoto | | Taguchi | | Watanabe | | Satou | | Morita | +--------------------------------+ 6 rows in set (0.01 sec)
NULL値が文字列に置換されています。
また、COALESCE関数を使っても、同様の結果を取得できます。
mysql> SELECT COALESCE(employee_name,'Morita') FROM employee; +----------------------------------+ | COALESCE(employee_name,'Morita') | +----------------------------------+ | Suzuki | | Hashimoto | | Taguchi | | Watanabe | | Satou | | Morita | +----------------------------------+ 6 rows in set (0.00 sec)
MySQLでNULL値を置換する場合は、「IFNULL関数」か「COALESCE関数」を使うと便利です！
PostgreSQLの場合
PostgreSQLでNULL値を置換する場合は、「COALESCE関数」を使いましょう。
使い方は、MySQLで紹介した通りです。
SELECT COALESCE(カラム名, '置き換える文字列') FROM テーブル名;
Oracleの場合
OracleでNULL値を置換する場合は、「NVL関数」か「COALESCE関数」を使います。
COALESCE関数の使い方は、他のDBMSで説明した通りです。
NVL関数は、次のように記述します。
SELECT NVL(カラム名, '置き換える文字列') FROM dual;
記述方法は、他の関数と同じです。
執筆者がOracleを使用していないため、実行結果は省略します。
SQLServerの場合
SQLServerでNULL値を置換する場合は、「IsNull関数」を使いましょう。
IsNull関数は、次のように記述します。
SELECT IsNull(カラム名, '置き換える文字列') FROM テーブル名;
IsNull関数も記述方法は、他の関数と同じです。
執筆者がSQLServerを使用していないため、実行結果は省略します。
