
【SQL】REPLACEで文字列を置換する方法
REPLACEを使うと単語の意味から察するとおり「置き換え」ができる関数です。
つまり文字列の中から「指定した文字」を「別の文字」に置換することができます。
例えば「SQL University」の「university」を「Univ.」に置き換えられます。
それでは、使い方を確認しましょう。
目次
1.REPLACEでできること
文字列操作をおこなう関数です。
文字列の中から指定した文字を別の文字に置き換えることができます。
例)
・「SQL 大学」を「SQL University」に置き換える
・「123-456-7890」のハイフンを除去する
・「あかさか たろう」のスペースを除去する
2.REPLACEの構文
REPLACEの構文は以下の通りです。
REPLACE(‘対象の文字列’(またはカラム名), ‘置換前の文字列’, ‘置換後の文字列’)
例えば次のSQL文では
「ABC12345678ABC」内の文字列「ABC」を「XYZ」に置き換えます。
SELECT REPLACE('ABC12345678ABC', 'ABC', 'XYZ')
SELECT結果
+—————————————–+
| REPLACE(‘ABC12345678ABC’, ‘ABC’, ‘XYZ’) |
+—————————————–+
| XYZ12345678XYZ |
+—————————————–+
1 row in set (0.00 sec)
3.REPLACEの具体的な使い方
それではサンプルコードと共に具体的な使い方を見ていきましょう。
3-1.特定の文字を置換する
最初は基本的な使用方法です。
・・・・・・・・・・・・・・・・・・・・・・・・・・
使用例:
「製品A2000」は「製品A1000」の後継商品である。
sales_listテーブル上は別名で登録されているが「製品A1000」「製品A2000」共に
「製品A2000」としてSELECTしたい。
・・・・・・・・・・・・・・・・・・・・・・・・・・
◆sales_list テーブル
customer_name | product | quantity | price | order_date |
AAA株式会社 | 製品A1000 | 167 | 12000 | 2024/3/10 |
ABC株式会社 | 製品A2000 | 280 | 12300 | 2024/10/11 |
株式会社XX | 製品B | 110 | 19000 | 2024/4/8 |
株式会社YY | 製品C | 76 | 80000 | 2024/6/20 |
ZZ株式会社 | 製品AB1000 | 160 | 600 | 2024/5/7 |
SELECT customer_name, REPLACE(product, '製品A1000', '製品A2000') as product, quantity, price, order_date FROM sales_list;
SELECT 結果
+—————–+————–+———-+——-+———————+
| customer_name | product | quantity | price | order_date |
+—————–+————–+———-+——-+———————+
| AAA株式会社 | 製品A2000 | 167 | 12000 | 2024-03-10 00:00:00 |
| ABC株式会社 | 製品A2000 | 280 | 12300 | 2024-10-11 00:00:00 |
| 株式会社XX | 製品B | 110 | 19000 | 2024-04-08 00:00:00 |
| 株式会社YY | 製品C | 76 | 80000 | 2024-06-20 00:00:00 |
| ZZ株式会社 | 製品AB1000 | 160 | 600 | 2024-05-07 00:00:00 |
+—————–+————–+———-+——-+———————+
5 rows in set (0.00 sec)
元のテーブルでは「AAA株式会社」のproductは「製品A1000」ですが、REPLACE後のSELECT結果では「製品A2000」に置換されています。
このとき、「1000」を「2000」にREPLACEするようなSQL文を記述してしまうと「製品AB1000」の値も置換されてしまうため、置換前の値の指定には充分注意してください。
3-2.文字列中の特定の文字を除去する
指定の文字を‘’で置換することにより、指定の文字を取り除くという使い方をすることができます。
・・・・・・・・・・・・・・・・・・・・・・・・・・
使用例:
customer_listテーブルの顧客IDのハイフンを取り除き、数字のみのCustomerIDを取得したい
・・・・・・・・・・・・・・・・・・・・・・・・・・
◆ customer_listテーブル
customer_name | CustomerID |
AAA株式会社 | 0000-111-2222 |
ABC株式会社 | 0000-111-2223 |
株式会社XX | 0000-111-2224 |
株式会社YY | 0000-111-2225 |
ZZ株式会社 | 0000-111-2226 |
株式会社XYZ | 1111-111-2226 |
SELECT customer_name, REPLACE(customerID, '-', '') as customerID FROM customer_list;
SELECT結果
+—————–+————-+
| customer_name | customerID |
+—————–+————-+
| AAA株式会社 | 00001112222 |
| ABC株式会社 | 00001112223 |
| 株式会社XX | 00001112224 |
| 株式会社YY | 00001112225 |
| ZZ株式会社 | 00001112225 |
| 株式会社XYZ | 11111112227 |
+—————–+————-+
6 rows in set (0.00 sec)
ハイフンを除去し、数字のみの顧客IDを取得することができました。
3-3.◯文字目から◯文字を置換対象とする(SUBSTRING・ CONCATと併せて使用)
応用編として特定の場所にある指定の文字を置き換える方法です。
先程も述べましたが、不用意にREPLACEを使用すると、思わぬ箇所も書き換わってしまう場合があります。
一定のルールに基づいて置換対象が存在する場合、SUBSTRINGと組み合わせる方法が使用できます。
例えば先程のcustomer_listテーブルの顧客IDの値6〜8文字目の「111」を「AAA」に置換してみます。
・・・・・・・・・・・・・・・・・・・・・・・・・・
使用例:
customer_listテーブルのcustomer ID の6文字目から8文字目の「111」を「AAA」に置換したい
・・・・・・・・・・・・・・・・・・・・・・・・・・
単純にREPLACEをすると対象外としたい文字列も置換されてしまうため少し工夫が必要となります。
ex) 株式会社XYZ 1111-111-2226 → 1111-111-2226 先頭の「1111-」は置換対象外としたい
方法はいくつか考えられますが、この例では以下のような処理をします。
① SUBSTRING を使用して先頭の5文字を取得する ←この部分は置換対象外
② SUBSTRING を使用して6文字目から8文字目を取得、REPLACE で「111」を「AAA」に置換する
③ SUBSTRING を使用して9文字目以降を取得する
④ CONCAT を使用して上記①から③の文字列を結合する
SELECT customer_name, CONCAT( SUBSTRING(customerID, 1, 5) , REPLACE(SUBSTRING(customerID, 6,3),'111','AAA'), SUBSTRING(customerID, 9) ) as customerID FROM customer_list;
SELECT結果
+—————–+—————+
| customer_name | customerID |
+—————–+—————+
| AAA株式会社 | 0000-AAA-2222 |
| ABC株式会社 | 0000-AAA-2223 |
| 株式会社XX | 0000-AAA-2224 |
| 株式会社YY | 0000-AAA-2225 |
| ZZ株式会社 | 0000-AAA-2225 |
| 株式会社XYZ | 1111-AAA-2227 |
+—————–+—————+
6 rows in set (0.00 sec)
6文字目から8文字目をREPLACEの対象としているため、株式会社XYZの顧客ID先頭の「1111-」は「AAA1-」に置換されることなく「1111-AAA-2227」という顧客IDを取得することができました。
ここではSUBSTRING・CONCATの詳細は記載しませんが、様々な関数と組み合わせて使用することで意図しない置換が行われないようにすることができます。
※SUBSTRING はDBMSにより表記が異なります。詳細はお使いのDB名で検索してみてください。
3-4.UPDATEと合わせて使用しテーブルの値を更新する
ここまでのSQLではあくまでもSELECT結果に対して置換をしていますのでDBのテーブル上の値に変化はありません。
テーブルの値を更新するには、UPDATE文と組み合わせて使用する必要があります。
4.さいごに
REPLACE関数について解説しました。
他にも「漢字とひらがなの表記揺れ統一」など、REPLACE関数の使いどころはたくさんありますが、意図しない置換がおこなわれないような工夫も必要です。
最後までお読みいただきましてありがとうございました。
【動作検証DB】
mySQL 8.0.39
コメント