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