SQL道場 文字列関数 REPLACE関数

最終更新日

SQLで文字列を置換するREPLACE関数について記載しています。

対応データベース:SQL Server MySQL Oracle PostgreSQL DB2 SQLite

REPLACE関数の文法

SELECT REPLACE(置換対象の文字列,検索文字列,置換文字列) FROM テーブル
引数置換対象の文字列文字列型
検索文字列文字列型
置換文字列文字列型
返値数値型 編集後の文字列が戻ります。

SQL Server

https://learn.microsoft.com/ja-jp/sql/t-sql/functions/replace-transact-sql?view=sql-server-ver16
引数置換対象の文字列検索する文字列式を指定します。
「置換対象の文字列」文字またはバイナリ データ型であることができます。
検索文字列検索するサブストリングです。
「検索文字列」 文字またはバイナリ データ型であることができます。
「検索文字列」は、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;
MySQL 置換対象の文字列を検索し該当した文字列を置換するサンプル実行結果

特定のカラム内容を一括置換する方法

以下のSQLで特定のカラムを一括置換することができます。何万件のデータがあっても一瞬で置換することができます。ただし、間違って置換されてしまうと元に戻すことができないため、実行前にはバックアップを取ること推奨します。

/* MySQL 特定のカラム内容を一括置換するサンプル */
UPDATE テーブル名 SET カラム = REPLACE(対象カラム,'検索文字列','置換文字')
/* 条件による一括置換 */
UPDATE テーブル名 SET カラム = REPLACE(対象カラム,'検索文字列','置換文字') WHERE 条件

大文字小文字の区別

/* MySQL 大文字小文字の区別サンプル */
SELECT STR,REPLACE(STR,'apple','orange') FROM testdb.WORK;

下記の例では、小文字のappleのみ置換されます。

 MySQL 大文字小文字の区別サンプル実行結果

改行文字コードの置換

  • CRLF:\r\n
  • CR:\r
  • LF:\n
/* MySQL 改行文字コードの置換サンプル */
UPDATE テーブル名 SET カラム名 = REPLACE(カラム名,'\r\n','');

SQLServerの実行例

/* SQL Server 置換対象の文字列を検索し該当した文字列を置換するサンプル */
SELECT STR,REPLACE(STR,'計画','決行') FROM [testdb].[dbo].[WORK];
SQL Server 置換対象の文字列を検索し該当した文字列を置換するサンプル実行結果

大文字小文字の区別

MySQLの場合は、大文字と小文字は区別されますがSQLServerでは大文字と小文字は区別されません。

/* SQL Server 大文字小文字の区別サンプル */
SELECT STR,REPLACE(STR,'apple','orange')  FROM [testdb].[dbo].[WORK];
SQL Server 大文字小文字の区別サンプル実行結果

すなりん

サイトの管理者「すなりん」です。現役のシステムエンジニアをしてます。要件定義からテスト、プロジェクトマネージャー、顧客調整、社内インフラなんでもこなします。いわゆるフルスタック・エンジニアです。 仕事柄色んな技術や製品を扱うことが多く、なかなか覚えているもの大変ですし、しばらくすると忘れてしまうことが多いので、自分なりの形で技術情報やIT関係の情報を掲載しています。 個人で仕事の合間をみながら記事の投稿やサイトを更新していますので、誤字や間違った内容があったりもしますので、閲覧者はご理解の上ご利用ください。