REGEXP_SUBSTR 関数 - Amazon Redshift
構文引数戻り型例

REGEXP_SUBSTR 関数

正規表現パターンで検索して、文字列から文字を返します。REGEXP_SUBSTR は SUBSTRING 関数 関数に似ていますが、文字列で正規表現パターンを検索することができます。この関数が正規表現を文字列内のどの文字とも一致させることができない場合、空の文字列を返します。正規表現の詳細については、Wikipedia の「POSIX 演算子」と「正規表現」を参照してください。

構文

REGEXP_SUBSTR( source_string, pattern [, position [, occurrence [, parameters ] ] ] )

引数

source_string

検索する文字列式。

pattern

正規表現パターンを表す UTF-8 文字列リテラル。詳細については、「POSIX 演算子」を参照してください。

position

検索を開始する source_string 内の位置を示す正の整数。位置はバイト数ではなく文字数に基づくため、マルチバイト文字は 1 文字としてカウントされます。デフォルトは 1 です。position が 1 より小さい場合、source_string の最初の文字から検索が開始されます。position が source_string の文字数より大きい場合、結果は空の文字列 ("") になります。

occurrence

このパターンのどの出現を使用するかを示す正の整数。REGEXP_SUBSTR は、最初の出現 - 1 一致をスキップします。デフォルトは 1 です。出現が 1 未満、またはソース文字列の文字数以上の場合、検索は無視され、結果は NULL となります。

パラメータ

関数がパターンと一致するかを示す 1 つ以上のリテラル文字列。取り得る値には以下のものがあります。

  • c – 大文字と小文字を区別する一致を実行します。デフォルトでは大文字と小文字を区別するマッチングを使用します。

  • i – 大文字と小文字を区別しない一致を実行します。

  • e – 部分式を使用して部分文字列を抽出します。

    パターンに部分式が含まれる場合、REGEXP_SUBSTR は最初の部分式をパターンで使用して部分文字列を一致させます。部分式は、かっこで囲まれたパターン内の式です。例えば、'This is a (\\w+)' というパターンでは、最初の式と 'This is a ' という文字列とそれに続く単語が一致します。e パラメータを指定した REGEXP_SUBSTR は、パターンを返す代わりに、部分式の中の文字列だけを返します。

    REGEXP_SUBSTR は最初の部分式のみを考慮します。追加の部分式は無視されます。パターンに部分式がない場合、REGEXP_SUBSTR は「e」パラメータを無視します。

  • p – Perl 互換正規表現 (PCRE) 言語でパターンを解釈します。PCRE の詳細については、Wikipedia の「Perl 互換の正規表現」を参照してください。

戻り型

VARCHAR

例

次の例では、メールアドレスの @ 文字とドメイン拡張の間の分が返されます。クエリされる users データは Amazon Redshift のサンプルデータからのものです。詳細については、「サンプルデータベース」を参照してください。

SELECT email, regexp_substr(email,'@[^.]*')
FROM users
ORDER BY userid LIMIT 4;

                     email                     |      regexp_substr
-----------------------------------------------+--------------------------
 Suspendisse.tristique@nonnisiAenean.edu       | @nonnisiAenean
 amet.faucibus.ut@condimentumegetvolutpat.ca   | @condimentumegetvolutpat
 sed@lacusUtnec.ca                             | @lacusUtnec
 Cum@accumsan.com                              | @accumsan

次の例では、大文字と小文字を区別しない一致を使用して、文字列 FOX の最初の出現に対応する入力の部分を返します。

SELECT regexp_substr('the fox', 'FOX', 1, 1, 'i');

 regexp_substr
---------------
 fox

次の例では、大文字と小文字を区別しない一致を使用して、文字列 FOX の 2 回目の出現に対応する入力部分を返します。2 回目の出現がないため、結果は NULL (空) になります。

SELECT regexp_substr('the fox', 'FOX', 1, 2, 'i');

 regexp_substr
---------------

次の例では、小文字で始まる入力の最初の部分を返します。これは、同じ SELECT ステートメントで c パラメータを指定しない場合と機能的には同じです。

SELECT regexp_substr('THE SECRET CODE IS THE LOWERCASE PART OF 1931abc0EZ.', '[a-z]+', 1, 1, 'c');

 regexp_substr
---------------
 abc

次の例では、PCRE 言語で記述されたパターンを使用して、少なくとも 1 つの数字と 1 つの小文字を含む単語を検索します。PCRE で特定の先読みの意味を持つ ?= 演算子を使用します。この例では、2 番目の単語に対応する入力部分を返します。

SELECT regexp_substr('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', 1, 2, 'p');

 regexp_substr
---------------
 a1234

次の例では、PCRE 言語で記述されたパターンを使用して、少なくとも 1 つの数字と 1 つの小文字を含む単語を検索します。PCRE で特定の先読みの意味を持つ ?= 演算子を使用します。この例では、2 番目の単語に対応する入力部分を返しますが、大文字と小文字を区別しない一致結果を使用する点で前の例とは異なります。

SELECT regexp_substr('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', 1, 2, 'ip');

 regexp_substr
---------------
 A1234

次の例では、部分式を使用して、大文字と小文字を区別しないマッチングにより、パターン 'this is a (\\w+)' と一致する 2 番目の文字列を見つけます。かっこ内の部分式を返します。

SELECT regexp_substr(
               'This is a cat, this is a dog. This is a mouse.',
               'this is a (\\w+)', 1, 2, 'ie');
           
 regexp_substr
---------------
 dog