SQLのreplaceについてまとめています。replaceは指定した文字列を別の文字列に置換する関数ですが、DBMSによっては別の機能を持つSQLとして使われているケースもあります。
SQL replaceの構文
REPLACE(str,from_str,to_str)
以下は、employees（社員）テーブルのfirst_nameの「Georgi」を「Mr.Georgi」に置換する例です。1回目のSQLでemployeesテーブルをそのまま抽出し、2回めのSQLでemp_no、birth_dateと、first_nameをreplaceで置換した文字列を抽出しています。
mysql> select * from employees limit 10; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | ： ： mysql> select emp_no, birth_date, replace(first_name, 'Georgi', 'Mr.Georgi') from employees limit 10; +--------+------------+--------------------------------------------+ | emp_no | birth_date | replace(first_name, 'Georgi', 'Mr.Georgi') | +--------+------------+--------------------------------------------+ | 10001 | 1953-09-02 | Mr.Georgi | | 10002 | 1964-06-02 | Bezalel | | 10003 | 1959-12-03 | Parto | | 10004 | 1954-05-01 | Chirstian | | 10005 | 1955-01-21 | Kyoichi | | 10006 | 1953-04-20 | Anneke | | 10007 | 1957-05-23 | Tzvetan | | 10008 | 1958-02-19 | Saniya | | 10009 | 1952-04-19 | Sumant | | 10010 | 1963-06-01 | Duangkaew | +--------+------------+--------------------------------------------+ 10 rows in set (0.00 sec)
【関連記事】
▶SQLのREPLACE関数を用いた置換機能 使用方法を徹底的に解説
また、複数の文字列を置換したい場合は、replace関数を入れ子にするか、対象文字列が多い場合はcase式と組み合わせるとスッキリ記述できます。
mysql> select replace(replace('dog and cat', 'dog', 'mouse' ), 'cat', 'neko');
+-----------------------------------------------------------------+
| replace(replace('dog and cat', 'dog', 'mouse' ), 'cat', 'neko') |
+-----------------------------------------------------------------+
| mouse and neko |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
【関連記事】
▶SQLで文字列の置換をするにはreplace関数使用。 複数置換をするサンプルコード
Oracleのreplaceの構文
以下はOracleのreplace関数使用の例です。「JACK and JOE」という文字列の「J」を「BL」に置換しています。
SELECT REPLACE('JACK and JUE','J','BL') "Changes"
FROM DUAL;
Changes
--------------
BLACK and BLUE
SQL Serverのreplaceの構文
以下は、SQL Serverのreplace関数使用の例です。「abcdefghicde」の「cde」を「xxx」に置換しています。
SELECT REPLACE('abcdefghicde','cde','xxx');
GO
------------
abxxxfghixxx
(1 row(s) affected)
関連）REPLACE (Transact-SQL) – SQL Server | Microsoft Docs
MySQLのreplaceの構文
以下は、MySQLのreplace関数の使用例です。
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
関連）MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.5 文字列関数
また、MySQLにはREPLACE構文があり、レコードの挿入及び、重複レコード削除＆挿入の機能があります。1回目のreplaceはinsert文と同じ動きをし、2回めのreplaceは重複データの削除とinsertを行います。
mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00'); Query OK, 1 row affected (0.04 sec) mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42'); Query OK, 2 rows affected (0.04 sec) mysql> SELECT * FROM test; +----+------+---------------------+ | id | data | ts | +----+------+---------------------+ | 1 | New | 2014-08-20 18:47:42 | +----+------+---------------------+ 1 row in set (0.00 sec)
関連）MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.8 REPLACE 構文
PostgreSQLのreplaceの構文
PostgreSQLのrepalce関数の使用例です。
replace( 'abcdefabcdef', 'cd', 'XX')
PostgreSQLには、正規表現での置換に対応した、regexp_replace関数が使用可能です。
regexp_replace('Thomas', '.[mN]a.', 'M')
まとめ
- replace関数は、指定のカラムや文字列を置換する関数
- MySQLでは、replace関数とは全く別の機能を持つreplace文が用意されている
- PostgreSQLでは、正規表現の置換が可能なregexp_replace関数が利用可能
