SQLのREPLACE関数を用いた置換機能　使用方法を徹底的に解説

SQLのREPLACEという関数。SQL文の中で文字と文字を置き換える機能として使用することができます。今回の記事では、SQLのREPLACE関数の主な機能をご紹介しつつ、実際に使用する方法をコードとともにご紹介していきましょう。

SQL REPLACE関数とは

SQL REPLACE関数とは、対象文字列やカラムの中から指定した文字を別の文字に置き換えることができる関数のことです。SQL REPLACE関数の構文は以下の通りです。

SELECT REPLACE(対象文字列・カラム, 指定した文字, 置き換えたい文字);

例えば、以下のようなテーブルがあるとしましょう。

+------+-------+----------+
| id   | staff | roll     |
+------+-------+----------+
|    1 | Bob   | Cooking  |
|    2 | Tom   | Cleaning |
|    3 | Cara  | Cooking  |
|    4 | Chris | Cleaning |
+------+-------+----------+

この中で、「Cooking」の役割をすべて「washing」に変えるとしましょう。その場合、以下のようにREPLACE関数を使用することができます。

SELECT id, staff, REPLACE(roll, 'Cooking', 'Washing') FROM rolls;

出力結果は以下の通りです。

+------+-------+-------------------------------------+
| id   | staff | REPLACE(roll, 'Cooking', 'Washing') |
+------+-------+-------------------------------------+
|    1 | Bob   | Washing                             |
|    2 | Tom   | Cleaning                            |
|    3 | Cara  | Washing                             |
|    4 | Chris | Cleaning                            |
+------+-------+-------------------------------------+

「Cooking」の項目がすべて「Washing」に変更されていることが分かりますね。このように、SQLのREPLACE関数を使用すれば文字列・カラムの中で指定した文字を他の文字に変更することが可能なのです。

【関連記事】
▶︎【実践編】SQLのIF構文で関数を作ってみよう！基礎的な使い方も解説！
▶︎SQLの達人への第一歩、結合と抽出を同時に処理するINNER JOINの使い方をマスターしよう

SQL REPLACE関数を使ってみよう

こちらでは、実際にMySQLというデータベースを使用してREPLACE関数を使ってみましょう。

準備

まずはMySQLを使用するための準備をしていきます。

MySQLのインストール・接続

まだMySQLをインストールしていない場合は、インストールしましょう。

$ brew install mysql

MySQLのインストールが終了したら、接続します。

$ sudo mysql -u root -p

データベースの作成

次にデータベースをMySQL上に作成し、使用するデータベースを指定しましょう。

mysql> CREATE DATABASE potepan;
Query OK, 1 row affected (0.00 sec)

mysql> USE potepan;

これでMySQLを使用する準備は完了しました。

SQL REPLACEを使って単純な文字列の置き換え

前述の例ではテーブルカラムの中身を置き換えましたが、SQLのREPLACE関数では単純な文字列の中の文字を別の文字へ置き換えることもできます。早速試してみましょう。

mysql> SELECT REPLACE('Retire', 'tire', 'try');
+----------------------------------+
| REPLACE('Retire', 'tire', 'try') |
+----------------------------------+
| Retry                            |
+----------------------------------+
1 row in set (0.00 sec)

このSQL文の意味がわからなかった方は、もう1度、REPLACE関数の構文を確認してみましょう。

SELECT REPLACE(対象文字列・カラム, 指定した文字, 置き換えたい文字);

上記のSQL文は「Retire」という文字列から「tire」という文字を探し出し、「try」という文字に置き換えるものとなります。

長文の置き換え

先ほどは短い英単語の中身を置き換えましたが、長文であったとしても置き換えすることができます。

mysql> SELECT REPLACE('アイウエオ　カキクケコ　サシスセソ　タチツテト　ナニヌネノ　ハヒフヘホ　マミムメモ　ヤユヨ　ラリルレロ　ワヲン','　','★');
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| REPLACE('アイウエオ　カキクケコ　サシスセソ　タチツテト　ナニヌネノ　ハヒフヘホ　マミムメモ　ヤユヨ　ラリルレロ　ワヲン','　','★')                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| アイウエオ★カキクケコ★サシスセソ★タチツテト★ナニヌネノ★ハヒフヘホ★マミムメモ★ヤユヨ★ラリ ルレロ★ワヲン                                                                                        |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

カラムの置き換え

最後にテーブルを作成して、その中のカラムの文字列を置き換えてみましょう。

テーブルの作成

まずはテーブルを作成します。

mysql> CREATE TABLE animals(animal VARCHAR(50), name VARCHAR(50));                         
Query OK, 0 rows affected (0.00 sec)

【関連記事】
▶︎SQL create tableでテーブル生成 DBMSごとに差異あり

レコードの登録

次にレコードを登録しましょう。

mysql> INSERT INTO animals VALUES('cat', 'Tom'), ('dog', 'Pochi'), ('tiger', 'Shima');
Query OK, 3 rows affected (0.00 sec)

【関連記事】
▶︎【こんな使い方も？】SQL insertの使用方法を徹底的に解説

animalsテーブルは以下のような状態になっています。

mysql> SELECT * FROM animals;
+--------+-------+
| animal | name  |
+--------+-------+
| cat    | Tom   |
| dog    | Pochi |
| tiger  | Shima |
+--------+-------+

REPLACE関数で置き換え

最後にREPLACE関数でカラム内の文字を置き換えてみましょう。nameカラムの「Tom」を「Tama」に変えてみます。

mysql> SELECT animal, REPLACE(name, 'Tom', 'Tama') FROM animals;
+--------+------------------------------+
| animal | REPLACE(name, 'Tom', 'Tama') |
+--------+------------------------------+
| cat    | Tama                         |
| dog    | Pochi                        |
| tiger  | Shima                        |
+--------+------------------------------+

無事、REPLACE関数によって置き換えが成功しました！このように指定することで、該当カラムの指定された文字を一気に置き換えることも可能です。

ポテパンダの一言メモ

文字列とは異なり、REPLACEの第一引数にカラムを指定する場合には「’（シングルクォーテーション）」で囲まないということに注意しましょう。

まとめ

今回の記事では、SQLのREPLACE関数の主な機能をご紹介しつつ、実際に使用する方法をコードとともにご紹介していきました。SQLで一気に文字を置き換えることができる便利な関数なので、積極的に使用していきましょう。

【関連記事】
▶︎SQLの達人への第一歩、結合と抽出を同時に処理するINNER JOINの使い方をマスターしよう
▶︎SQL betweenで範囲指定するサンプルコード 速度アップにもつながる？
▶︎SQL distinctのサンプルコード集 group byよりも700倍速い？