1. SQL言語リファレンス
  2. ファンクション
  3. REGEXP_REPLACE

REGEXP_REPLACE

構文

regexp_replace.epsの説明が続きます
図regexp_replace.epsの説明

目的

REGEXP_REPLACEは、正規表現パターンで文字列を検索できるようにREPLACEの機能を拡張したものです。デフォルトでは、このファンクションは、正規表現パターンのすべての出現箇所をreplace_stringに置き換えてsource_charを戻します。source_charと同じ文字セットの文字列が戻されます。このファンクションは、1つ目の引数がLOBではない場合はVARCHAR2を戻し、1つ目の引数がLOBの場合はCLOBを戻します。

このファンクションは、POSIX正規表現規格およびUnicode Regular Expression Guidelinesに準拠しています。詳細は、「Oracleの正規表現のサポート」を参照してください。

  • source_charは、検索値として使用される文字式です。一般的には文字の列であり、データ型はCHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB、NCLOBのいずれかになります。

  • patternは正規表現です。通常はテキスト・リテラルであり、データ型はCHAR、VARCHAR2、NCHARまたはNVARCHAR2のいずれかになります。ここには、 512バイトまで入力できます。patternのデータ型がsource_charのデータ型と異なる場合、Oracleはpatternをsource_charのデータ型に変換します。patternで指定できる演算子のリストは、「Oracleの正規表現のサポート」を参照してください。

  • replace_stringは、CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOBまたはNCLOBデータ型です。replace_stringがCLOBまたはNCLOBの場合、replace_stringは32KBに切り捨てられます。replace_stringには、最大500個の部分正規表現への後方参照を\nという書式で指定できます。nは、1から9の数値です。replace_stringにバックスラッシュ(\)を含める場合、その前にエスケープ文字(これもバックスラッシュ)を付ける必要があります。たとえば、\2を置き換えるには、\\2を入力します。後方参照表現の詳細は、「Oracleの正規表現のサポート」の表D-1に示すノートを参照してください。

  • positionは、Oracleが検索を開始する文字source_charの位置を示す正の整数です。デフォルトは1で、source_charの最初の文字から検索が開始されます。

  • occurrenceは、置換操作の対象となる文字を示す、負ではない整数です。

    • 0(ゼロ)を指定すると、一致したすべての文字が置換されます。

    • 正の整数nを指定すると、n番目に一致した文字が置換されます。

    occurrenceが1より大きい場合、1番目のpatternが検出された後に続く1文字目から、2番目(以降)の出現を検索します。この動作は、最初の出現の2番目の文字から2番目の出現を検索するINSTRファンクションとは異なります。

  • match_paramは、データ型VARCHAR2またはCHARの文字式で、ファンクションのデフォルトの一致動作を変更できます。このパラメータの動作は、REGEXP_COUNTのこのファンクションの動作と同じです。詳細は、「REGEXP_COUNT」を参照してください。

関連項目:

例

次の例では、phone_numberを調べて、xxx.xxx.xxxxというパターンを検索します。その後、このパターンを(xxx) xxx-xxxxという書式に変更します。 

SELECT
  REGEXP_REPLACE(phone_number,
                 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                 '(\1) \2-\3') "REGEXP_REPLACE"
  FROM employees
  ORDER BY "REGEXP_REPLACE";

REGEXP_REPLACE
--------------------------------------------------------------------------------
(515) 123-4444
(515) 123-4567
(515) 123-4568
(515) 123-4569
(515) 123-5555
. . .

次の例では、country_nameを調べます。文字列内のNULLでない各文字の後に空白を挿入します。 

SELECT
  REGEXP_REPLACE(country_name, '(.)', '\1 ') "REGEXP_REPLACE"
  FROM countries;

REGEXP_REPLACE
--------------------------------------------------------------------------------
A r g e n t i n a
A u s t r a l i a
B e l g i u m
B r a z i l
C a n a d a
. . .

次の例では、文字列を調べて、2つ以上の空白を検索します。検索された2つ以上の空白を、それぞれ1つの空白に置換します。

SELECT
  REGEXP_REPLACE('500   Oracle     Parkway,    Redwood  Shores, CA',
                 '( ){2,}', ' ') "REGEXP_REPLACE"
  FROM DUAL;

REGEXP_REPLACE
--------------------------------------
500 Oracle Parkway, Redwood Shores, CA

REGEXP_REPLACEのパターン一致: 例

次の文は、表regexp_tempを作成し、そこに値を挿入します。

CREATE TABLE regexp_temp(empName varchar2(20), emailID varchar2(20));

INSERT INTO regexp_temp (empName, emailID) VALUES ('John Doe', 'johndoe@example.com');
INSERT INTO regexp_temp (empName, emailID) VALUES ('Jane Doe', 'janedoe@example.com');

次の文は、文字列'Jane'を'John'に置き換えます。

SELECT empName, REGEXP_REPLACE (empName, 'Jane', 'John') "STRING_REPLACE" FROM regexp_temp;

EMPNAME		STRING_REPLACE
--------	--------------
John Doe	John Doe
Jane Doe	John Doe

次の文は、文字列'John'を'Jane'に置き換えます。

SELECT empName, REGEXP_REPLACE (empName, 'Jane', 'John') "STRING_REPLACE" FROM regexp_temp;

EMPNAME		STRING_REPLACE
--------	--------------
John Doe	Jane Doe
Jane Doe	Jane Doe

Live SQL:

REGEXP_REPLACE - Pattern Matchingで、Oracle Live SQLに関連する例を参照および実行します

REGEXP_REPLACE: 例

次の文は、文字列内のすべての数値を置換します。

WITH strings AS (   
  SELECT 'abc123' s FROM dual union all   
  SELECT '123abc' s FROM dual union all   
  SELECT 'a1b2c3' s FROM dual   
)   
  SELECT s "STRING", regexp_replace(s, '[0-9]', '') "MODIFIED_STRING"  
  FROM strings;

  STRING               MODIFIED_STRING
-------------------- --------------------
abc123               abc
123abc               abc
a1b2c3               abc

次の文は、文字列で最初に出現した数値を置き換えます。

WITH strings AS (   
  SELECT 'abc123' s from DUAL union all   
  SELECT '123abc' s from DUAL union all   
  SELECT 'a1b2c3' s from DUAL   
)   
  SELECT s "STRING", REGEXP_REPLACE(s, '[0-9]', '', 1, 1) "MODIFIED_STRING"  
  FROM   strings;

 STRING               MODIFIED_STRING
-------------------- --------------------
abc123               abc23
123abc               23abc
a1b2c3               ab2c3

次の文は、文字列で2番目に出現した数値を置き換えます。

WITH strings AS (   
  SELECT 'abc123' s from DUAL union all   
  SELECT '123abc' s from DUAL union all   
  SELECT 'a1b2c3' s from DUAL   
)   
  SELECT s "STRING", REGEXP_REPLACE(s, '[0-9]', '', 1, 2) "MODIFIED_STRING"  
  FROM   strings;

STRING               MODIFIED_STRING
-------------------- --------------------
abc123               abc13
123abc               13abc
a1b2c3               a1bc3

次の文は、文字列内の複数のスペースを1つのスペースに置換します。

WITH strings AS (   
  SELECT 'Hello  World' s FROM dual union all   
  SELECT 'Hello        World' s FROM dual union all   
  SELECT 'Hello,   World  !' s FROM dual   
)   
  SELECT s "STRING", regexp_replace(s, ' {2,}', ' ') "MODIFIED_STRING"  
  FROM   strings;

 STRING               MODIFIED_STRING
-------------------- --------------------
Hello  World         Hello World
Hello        World   Hello World
Hello,   World  !    Hello, World !

次の文は、キャメル・ケース表記の文字列を、アンダースコアで区切られた小文字を含む文字列に変換します。

WITH strings as (   
  SELECT 'AddressLine1' s FROM dual union all   
  SELECT 'ZipCode' s FROM dual union all   
  SELECT 'Country' s FROM dual   
)   
  SELECT s "STRING",  
         lower(regexp_replace(s, '([A-Z0-9])', '_\1', 2)) "MODIFIED_STRING"  
  FROM strings;

  STRING               MODIFIED_STRING
-------------------- --------------------
AddressLine1         address_line_1
ZipCode              zip_code
Country              country

次の文は、日付の書式を変換します。

WITH date_strings AS (   
  SELECT  '2015-01-01' d from dual union all   
  SELECT '2000-12-31' d from dual union all   
  SELECT '900-01-01' d from dual   
)   
  SELECT d "STRING",   
         regexp_replace(d, '([[:digit:]]+)-([[:digit:]]{2})-([[:digit:]]{2})', '\3.\2.\1') "MODIFIED_STRING"  
  FROM date_strings;

  STRING               MODIFIED_STRING
-------------------- --------------------
2015-01-01           01.01.2015
2000-12-31           31.12.2000
900-01-01            01.01.900

次の文は、文字列内のすべての文字を'1'に置き換えます。

WITH strings as (   
  SELECT 'NEW YORK' s FROM dual union all   
  SELECT 'New York' s FROM dual union all   
  SELECT 'new york' s FROM dual   
)   
  SELECT s "STRING",  
        regexp_replace(s, '[a-z]', '1', 1, 0, 'i') "CASE_INSENSITIVE",  
        regexp_replace(s, '[a-z]', '1', 1, 0, 'c') "CASE_SENSITIVE",  
        regexp_replace(s, '[a-zA-Z]', '1', 1, 0, 'c') "CASE_SENSITIVE_MATCHING"  
  FROM  strings;

  STRING     CASE_INSEN CASE_SENSI CASE_SENSI
---------- ---------- ---------- ----------
NEW YORK   111 1111   NEW YORK   111 1111
New York   111 1111   N11 Y111   111 1111
new york   111 1111   111 1111   111 1111

Live SQL:

REGEXP_REPLACEで、Oracle Live SQLに関連する例を参照および実行します