SQL道場 文字列関数 REPLACE関数
SQLで文字列を置換するREPLACE関数について記載しています。
対応データベース：SQL Server MySQL Oracle PostgreSQL DB2 SQLLite
REPLACE関数の文法
SELECT REPLACE(置換対象の文字列,検索文字列,置換文字列) FROM テーブル| 引数 | 置換対象の文字列 | 文字列型 |
| 検索文字列 | 文字列型 | |
| 置換文字列 | 文字列型 | |
| 返値 | 数値型 編集後の文字列が戻ります。 |
SQL Server（マイクロソフト公式）
| 引数 | 置換対象の文字列 | 検索する文字列式を指定します。 「置換対象の文字列」文字またはバイナリ データ型であることができます。 |
| 検索文字列 | 検索するサブストリングです。 「検索文字列」 文字またはバイナリ データ型であることができます。 「検索文字列」は、1 ページに収まる最大バイト数を超えないようにしてください。 「検索文字列」が空の文字列 (”) の場合、「置換対象の文字列」は変更されずに返されます。 | |
| 置換文字列 | 置き換え後の文字列です。「置換文字列」文字またはバイナリ データ型であることができます。 | |
| 返値 | 返します nvarchar が、入力引数のいずれかの場合、 nvarchar データが入力のそれ以外の場合を返します。 を置き換える varchar です。 いずれかの引数が NULL の場合は、NULL を返します。 場合 「置換対象の文字列」 の種類はありません varchar (max) または nvarchar (max)、 置換 は 8,000 バイトで戻り値を切り捨てます。 8,000 バイトを超える値を返すには、大きな値を格納できるデータ型に 「置換対象の文字列」を明示的にキャストする必要があります。 |
REPLACE関数は置換対象の文字列を検索し、該当した文字列を置換します。また、MySQLでは置換する際に大文字と小文字を区別して検索置換しています。
SQL Server
REPLACE は、入力の照合順序に基づいて比較を行います。 特定の照合順序で比較を行うには、COLLATE を使用して、入力に明示的な照合順序を適用します。
REPLACE 関数はマルチバイト文字も使用できます。
実行例
MySQLの実行例
/* MySQL 置換対象の文字列を検索し該当した文字列を置換するサンプル */
SELECT STR,REPLACE(STR,'計画','決行') FROM testdb.WORK;
特定のカラム内容を一括置換する方法
以下のSQLで特定のカラムを一括置換することができます。何万件のデータがあっても一瞬で置換することができます。ただし、間違って置換されてしまうと元に戻すことができないため、実行前にはバックアップを取ること推奨します。
/* MySQL 特定のカラム内容を一括置換するサンプル */
UPDATE テーブル名 SET カラム = REPLACE(対象カラム,'検索文字列','置換文字')
/* 条件による一括置換 */
UPDATE テーブル名 SET カラム = REPLACE(対象カラム,'検索文字列','置換文字') WHERE 条件大文字小文字の区別
/* MySQL 大文字小文字の区別サンプル */
SELECT STR,REPLACE(STR,'apple','orange') FROM testdb.WORK;下記の例では、小文字のappleのみ置換されます。
改行文字コードの置換
- CRLF：\r\n
- CR：\r
- LF：\n
/* MySQL 改行文字コードの置換サンプル */
UPDATE テーブル名 SET カラム名 = REPLACE(カラム名,'\r\n','');SQLServerの実行例
/* SQL Server 置換対象の文字列を検索し該当した文字列を置換するサンプル */
SELECT STR,REPLACE(STR,'計画','決行') FROM [testdb].[dbo].[WORK];
大文字小文字の区別
MySQLの場合は、大文字と小文字は区別されますがSQLServerでは大文字と小文字は区別されません。
/* SQL Server 大文字小文字の区別サンプル */
SELECT STR,REPLACE(STR,'apple','orange') FROM [testdb].[dbo].[WORK];