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

REGEXP_SUBSTR

構文

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

目的

REGEXP_SUBSTRは、正規表現パターンで文字列を検索できるようにSUBSTRの機能を拡張したものです。REGEXP_INSTRと似ていますが、このファンクションはサブストリングの位置ではなくサブストリング自体を戻します。このファンクションは、一致文字列の内容のみが必要で、ソース文字列内での位置は必要ない場合に有効です。このファンクションは、文字列をVARCHAR2またはCLOBデータとして、source_charと同じ文字セットで戻します。

このファンクションは、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の正規表現のサポート」を参照してください。

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

  • occurrenceは、source_charに出現するpatternのうちのどれを検索するかを示す正の整数です。デフォルトは1で、最初に出現するpatternが検索されます。

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

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

  • 部分正規表現のあるpatternの場合、subexprは、ファンクションによって戻されるpattern内の部分正規表現を示す0から9までの負でない整数になります。このパラメータは、REGEXP_INSTRファンクションの場合と同じセマンティクスを持ちます。詳細は、「REGEXP_INSTR」を参照してください。

関連項目:

例

次の例では、文字列を調べて、カンマで区切られた最初のサブストリングを検索します。Oracle Databaseは、後にカンマが付いているカンマでない1つ以上の文字の前にあるカンマを検索します。該当するサブストリングが、前後のカンマを含めて戻されます。

SELECT
  REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',
                ',[^,]+,') "REGEXPR_SUBSTR"
  FROM DUAL;

REGEXPR_SUBSTR
-----------------
, Redwood Shores,

次の例では、文字列を調べて、1つ以上の英数字を含むサブストリング、および任意でピリオド(.)が続くhttp://を検索します。Oracleは、http://と、スラッシュ(/)または文字列の末尾のいずれかとの間で、3つから4つのこのサブストリングを検索します。 

SELECT
  REGEXP_SUBSTR('http://www.example.com/products',
                'http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR"
  FROM DUAL;

REGEXP_SUBSTR
----------------------
http://www.example.com/

次の2つの例では、subexpr引数を使用してpattern内の特定の部分正規表現を戻します。最初の文は、pattern内の最初の部分正規表現を戻します。 

SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 1) 
"REGEXP_SUBSTR" FROM DUAL;

REGEXP_SUBSTR
-------------------
123

次の文は、pattern内の4番目の部分正規表現を戻します。 

SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 4) 
"REGEXP_SUBSTR" FROM DUAL;

REGEXP_SUBSTR
-------------------
78

REGEXP_SUBSTRのパターン一致: 例

次の文は、表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');

次の例では、この文は電子メールの列を問合せ、有効な電子メール・アドレスを検索します。

SELECT empName, REGEXP_SUBSTR(emailID, '[[:alnum:]]+\@[[:alnum:]]+\.[[:alnum:]]+') "Valid Email" FROM regexp_temp;

EMPNAME  Valid Email
-------- -------------------
John Doe johndoe@example.com
Jane Doe

次の例では、この文は電子メールの列を問合せ、有効な電子メール・アドレスの数を戻します。

SELECT empName, REGEXP_SUBSTR(emailID, '[[:alnum:]]+\@[[:alnum:]]+\.[[:alnum:]]+') "Valid Email", REGEXP_INSTR(emailID, '\w+@\w+(\.\w+)+') "FIELD_WITH_VALID_EMAIL" FROM regexp_temp;

EMPNAME		Valid Email			FIELD_WITH_VALID_EMAIL
--------	-------------------	----------------------
John Doe	johndoe@example.com	1
Jane Doe

Live SQL:

REGEXP_SUBSTRのパターン一致で、Oracle Live SQLに関連する例を参照および実行します

次の例では、数字およびアルファベットが文字列から抽出されます。

with strings as (   
  select 'ABC123' str from dual union all   
  select 'A1B2C3' str from dual union all   
  select '123ABC' str from dual union all   
  select '1A2B3C' str from dual   
)   
  select regexp_substr(str, '[0-9]') First_Occurrence_of_Number,    
         regexp_substr(str, '[0-9].*') Num_Followed_by_String,   
         regexp_substr(str, '[A-Z][0-9]') Letter_Followed_by_String   
  from strings;

FIRST_OCCURRENCE_OF_NUMB NUM_FOLLOWED_BY_STRING   LETTER_FOLLOWED_BY_STRIN
------------------------ ------------------------ ------------------------
1			 123			  C1
1			 1B2C3			  A1
1			 123ABC
1			 1A2B3C 		  A2

Live SQL:

REGEXP_SUBSTR - 数字およびアルファベットの抽出で、Oracle Live SQLに関連する例を参照および実行します

次の例では、乗客名およびフライト情報が文字列から抽出されます。

with strings as (    
  select 'LHRJFK/010315/JOHNDOE' str from dual union all    
  select 'CDGLAX/050515/JANEDOE' str from dual union all    
  select 'LAXCDG/220515/JOHNDOE' str from dual union all    
  select 'SFOJFK/010615/JANEDOE' str from dual    
)    
  SELECT regexp_substr(str, '[A-Z]{6}') String_of_6_characters,   
         regexp_substr(str, '[0-9]+') First_Matching_Numbers,   
         regexp_substr(str, '[A-Z].*$') Letter_by_other_characters,     
         regexp_substr(str, '/[A-Z].*$') Slash_letter_and_characters     
  FROM strings;

STRING_OF_6_CHARACTERS	FIRST_MATCHING_NUMBERS	LETTER_BY_OTHER_CHARACTERS	SLASH_LETTER_AND_CHARACTERS
----------------------	----------------------	--------------------------	---------------------------
LHRJFK	                010315	                LHRJFK/010315/JOHNDOE	      	/JOHNDOE
CDGLAX	                050515	                CDGLAX/050515/JANEDOE	      	/JANEDOE
LAXCDG	                220515	                LAXCDG/220515/JOHNDOE	      	/JOHNDOE
SFOJFK	                010615	                SFOJFK/010615/JANEDOE	      	/JANEDOE

Live SQL:

REGEXP_SUBSTR - 乗客名およびフライト情報の抽出で、Oracle Live SQLに関連する例を参照および実行します