SUBSTRING、REPLACE、PATINDEXを使いこなす！SQL Serverで文字列を自在に操る
SQL Serverで文字列の一部を更新・置換する方法
本記事では、以下の3つの方法について解説します。
SUBSTRINGとLEN関数REPLACE関数PATINDEXとUPDATE関数
それぞれの方法について、具体的なコード例と詳細な説明を提供します。
前提条件
以下の環境を想定しています。
- SQL Server 2017以降
- サンプルデータベース
AdventureWorks2019
SUBSTRING と LEN 関数
SUBSTRING 関数は、文字列から指定された部分文字列を取得します。LEN 関数は、文字列の長さを取得します。これらの関数を組み合わせることで、文字列の一部を更新・置換することができます。
例：
Products テーブルの Name 列に、"自転車" という文字列が含まれるすべての製品名を "電動自転車" に更新します。
UPDATE Products
SET Name = SUBSTRING(Name, 1, LEN(Name) - 2) + '電動自転車'
WHERE Name LIKE '%自転車%';
解説：
SUBSTRING(Name, 1, LEN(Name) - 2)：Name列の最初の文字から、最後の2文字を除いた部分文字列を取得します。+ '電動自転車'：取得した部分文字列に "電動自転車" を連結します。WHERE Name LIKE '%自転車%'：Name列に "自転車" という文字列が含まれる製品のみを更新します。
REPLACE 関数は、文字列中の指定された文字列を別の文字列に置換します。
Customers テーブルの ContactTitle 列に、"Mr." という文字列を "Ms." に置換します。
UPDATE Customers
SET ContactTitle = REPLACE(ContactTitle, 'Mr.', 'Ms.');
REPLACE(ContactTitle, 'Mr.', 'Ms.')：ContactTitle列の "Mr." という文字列を "Ms." に置換します。
PATINDEX と UPDATE 関数
PATINDEX 関数は、文字列パターンに一致する最初の位置を返します。
Employees テーブルの FirstName 列に、数字が含まれるすべての名前の先頭に "数字" という文字列を追加します。
UPDATE Employees
SET FirstName = '数字' + FirstName
WHERE PATINDEX('[0-9]', FirstName) > 0;
PATINDEX('[0-9]', FirstName) > 0：FirstName列に数字が含まれる場合、PATINDEX関数は 0 より大きい値を返します。'数字' + FirstName：名前の先頭に "数字" という文字列を追加します。
上記3つの方法を使い分けることで、様々なパターンで文字列の一部を更新・置換することができます。
補足
- 上記の例は基本的な方法を紹介していますが、より複雑な置換処理を行う場合は、CASE 式やその他の関数を利用することもできます。
- 更新処理を実行する前に、必ずバックアップを取ることを忘れないでください。
SUBSTRING と LEN 関数
-- サンプルデータベース AdventureWorks2019 を使用する
USE AdventureWorks2019;
-- Products テーブルの Name 列に "自転車" という文字列が含まれる
-- すべての製品名を "電動自転車" に更新
UPDATE Products
SET Name = SUBSTRING(Name, 1, LEN(Name) - 2) + '電動自転車'
WHERE Name LIKE '%自転車%';
-- 更新結果を確認
SELECT Name
FROM Products
WHERE Name LIKE '%電動自転車%';
REPLACE 関数
-- サンプルデータベース AdventureWorks2019 を使用する
USE AdventureWorks2019;
-- Customers テーブルの ContactTitle 列に "Mr." という文字列を
-- "Ms." に置換
UPDATE Customers
SET ContactTitle = REPLACE(ContactTitle, 'Mr.', 'Ms.');
-- 更新結果を確認
SELECT ContactTitle
FROM Customers
WHERE ContactTitle = 'Ms.';
PATINDEX と UPDATE 関数
-- サンプルデータベース AdventureWorks2019 を使用する
USE AdventureWorks2019;
-- Employees テーブルの FirstName 列に数字が含まれる
-- すべての名前の先頭に "数字" という文字列を追加
UPDATE Employees
SET FirstName = '数字' + FirstName
WHERE PATINDEX('[0-9]', FirstName) > 0;
-- 更新結果を確認
SELECT FirstName
FROM Employees
WHERE FirstName LIKE '数字%';
実行方法
- SQL Server Management Studio (SSMS) を起動します。
- サンプルコードをコピーして、SSMS のクエリ エディターに貼り付けます。
- クエリ エディター ツールバーの [実行] ボタンをクリックします。
- 結果ペインで、更新結果を確認します。
その他の文字列更新・置換方法
Products テーブルの Description 列に、"**" という文字列を商品名の後に挿入します。
UPDATE Products
SET Description = STUFF(Description, LEN(Name) + 2, 0, ' **');
STUFF(Description, LEN(Name) + 2, 0, ' **')：Description列に- 商品名の長さ (
LEN(Name)) + 2 番目 - 0 文字挿入
- " **" を挿入
Customers テーブルの Email 列に、"@example.com" という文字列が含まれるすべてのメールアドレスの先頭に "sales." を追加します。
UPDATE Customers
SET Email = 'sales.' + Email
WHERE CHARINDEX('@example.com', Email) > 0;
CHARINDEX('@example.com', Email) > 0：Email列に "@example.com" という文字列が含まれる場合、CHARINDEX関数は 0 より大きい値を返します。'sales.' + Email：メールアドレスの先頭に "sales." という文字列を追加します。
正規表現と UPDATE 関数
SQL Server 2016 以降では、正規表現を使用して文字列を更新・置換することができます。
UPDATE Employees
SET LastName = REPLACE(LastName, '^([0-9]+).*', '数字\1');
REPLACE(LastName, '^([0-9]+).*', '数字\1')：LastName列を- 正規表現
^([0-9]+).*で置換 \1は最初のキャプチャグループの内容
上記以外にも、様々な方法があります。
最適な方法を選択するには、以下の点を考慮する必要があります。
- 更新対象となる文字列のパターン
- 置換処理の複雑さ
- パフォーマンス
いくつかの方法を試してみて、要件に合致する最適な方法を選択してください。
sql sql-server string
