【SQL】CASEを使用して条件分岐する方法をわかりやすく解説

【SQL】REPLACEで文字列を置換する方法

Rii

Rii

ボールド歴8年

REPLACEを使うと単語の意味から察するとおり「置き換え」ができる関数です。
つまり文字列の中から「指定した文字」を「別の文字」に置換することができます。

例えば「SQL University」の「university」を「Univ.」に置き換えられます。
それでは、使い方を確認しましょう。


1.REPLACEでできること

文字列操作をおこなう関数です。
文字列の中から指定した文字を別の文字に置き換えることができます。

例)
・「SQL 大学」を「SQL University」に置き換える
・「123-456-7890」のハイフンを除去する
・「あかさか たろう」のスペースを除去する

REPLACE


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)

REPLACE2


3.REPLACEの具体的な使い方

それではサンプルコードと共に具体的な使い方を見ていきましょう。

3-1.特定の文字を置換する

最初は基本的な使用方法です。

・・・・・・・・・・・・・・・・・・・・・・・・・・
使用例:
「製品A2000」は「製品A1000」の後継商品である。
sales_listテーブル上は別名で登録されているが「製品A1000」「製品A2000」共に
「製品A2000」としてSELECTしたい。
・・・・・・・・・・・・・・・・・・・・・・・・・・

◆sales_list テーブル

customer_nameproductquantitypriceorder_date
AAA株式会社製品A1000167120002024/3/10
ABC株式会社製品A2000280123002024/10/11
株式会社XX製品B110190002024/4/8
株式会社YY製品C76800002024/6/20
ZZ株式会社製品AB10001606002024/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_nameCustomerID
AAA株式会社0000-111-2222
ABC株式会社0000-111-2223
株式会社XX0000-111-2224
株式会社YY0000-111-2225
ZZ株式会社0000-111-2226
株式会社XYZ1111-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

私たちは、全てのエンジニアに市場価値を高め自身の望む理想のキャリアを歩んでいただきたいと考えています。もし、今あなたが転職を検討しているのであればこちらの記事をご一読ください。理想のキャリアを実現するためのヒントが見つかるはずです。

『技術力』と『人間力』を高め定年まで働けるエンジニアを目指しませんか?

私たちは「技術力」だけでなく「人間力」の向上をもって、エンジニアとしてだけでなくビジネスパーソンとして高い水準を目指し、社会や顧客に必要とされることで、関わる人々に感動を与える集団であろうと思っています。

  • 定年までIT業界で働くためのスキルが身につく「感動大学」と「技術勉強会」!
  • 「給与が上がらない」を解消する6ヶ月に1度の明確な「人事評価制度」!
  • 理想のエンジニア像に近づくためのよきアドバイザー「専任コーチ制度」!
  • 稼動確認の徹底により実現できる平均残業時間17時間の働きやすい環境!

現在、株式会社ボールドでは「キャリア採用」のエントリーを受付中です。

まずは以下のボタンより弊社の紹介をご覧いただき、あなたの望むキャリアビジョンをエントリーフォームより詳しくお聞かせください。

コメント

IT業界を目指す求職者へ

プレミアムSESで市場価値の高いエンジニアへ

株式会社ボールドが約束する5つのプレミアムとは?

IT業界を目指す求職者へ

プレミアムSESで市場価値の高いエンジニアへ

株式会社ボールドが約束する5つのプレミアムとは?