REGEXP_REPLACE
構文
目的
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」を参照してください。
関連項目:
-
「REGEXP_INSTR」、「REGEXP_SUBSTR」および「REGEXP_LIKE条件」を参照してください。
-
source_charの文字をpatternの文字と比較するためにREGEXP_REPLACEで使用する照合を定義する照合決定ルール、およびこのファンクションの文字の戻り値に割り当てる照合を定義する照合導出ルールは、『Oracle Databaseグローバリゼーション・サポート・ガイド』の付録Cを参照してください。
例
次の例では、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, CAREGEXP_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 1111Live SQL:
REGEXP_REPLACEで、Oracle Live SQLに関連する例を参照および実行します