文字列関数(正規表現)

これらの文字列関数は、正規表現(「正規表現」と呼ばれることが多い)に一致する操作を実行します。

このトピックの内容:

正規表現関数のリスト

関数

メモ

REGEXP

RLIKE のエイリアス。

REGEXP_COUNT

REGEXP_EXTRACT_ALL

REGEXP_SUBSTR_ALL のエイリアス。

REGEXP_INSTR

REGEXP_LIKE

RLIKE のエイリアス。

REGEXP_REPLACE

REGEXP_SUBSTR

REGEXP_SUBSTR_ALL

RLIKE

一般的な使用上の注意

これらの注意では、「サブジェクト」は操作対象の文字列を指し、「パターン」は正規表現を指します。

  • 通常、サブジェクトは可変列であり、パターンは定数ですが、これは必須ではありません。正規表現関数のすべての引数は、定数または変数のいずれかにできます。

  • パターンは完全な POSIX ERE (拡張正規表現)構文をサポートします。詳細については、 POSIX 基本および拡張 セクション(ウィキペディア)をご参照ください。

  • パターンは、次のPerlバックスラッシュシーケンスもサポートしています。

    • \d:10進数(0~9)。

    • \D:10進数ではありません。

    • \s:空白文字。

    • \S:空白文字ではありません。

    • \w:「単語」文字(a~z、A~Z、アンダースコア(「_」)、または10進数)。

    • \W:単語の文字ではありません。

    • \b:単語の境界。

    • \B:単語の境界ではありません。

    詳細については、 文字クラス セクション(Wikipedia内)または バックスラッシュシーケンス セクション(Perlドキュメント内)をご参照ください。

    注釈

    一重引用符で囲まれた文字列定数 では、バックスラッシュシーケンスのバックスラッシュをエスケープする必要があります。たとえば、 \d を指定するには、 \\d を使用します。詳細については、 一重引用符で囲まれた文字列定数での正規表現の指定 (このトピック内)をご参照ください。

    文字列を ドル記号のペア($$) (一重引用符ではなく)で区切る場合は、バックスラッシュをエスケープする必要はありません。

  • デフォルトでは、 POSIX ワイルドカード文字 . (パターン内)には一致する改行文字 \n (サブジェクト内)は含まれません。

    改行文字にも一致させるには 、 パターン 引数で .(.|\n) に置き換えるか、 パラメーター 引数で s パラメーターを使用します(以下を参照)。

  • すべての正規表現関数はUnicodeをサポートしています。単一のUnicode文字は、その文字の対応するバイナリ表現のバイト長に関係なく、常に1文字としてカウントされます( POSIX メタ文字 . は正確に1つのUnicode文字に一致します)。また、サブジェクトオフセットを取得または返す関数の場合、単一のUnicode文字は1としてカウントされます。

正規表現のパラメーターの指定

ほとんどの正規表現関数は、最後の入力としてオプションの パラメーター 引数をサポートしています。 パラメーター 引数は、正規表現関数の一致動作に特化する VARCHAR 文字列です。次のパラメーターがサポートされています。

パラメーター

効果

c

大文字と小文字を区別した一致を有効にします。

i

大文字と小文字を区別しない一致を有効にします。

m

複数行モードを有効にします(メタ文字 ^ および $ は、件名の行の先頭と末尾をマークします)。デフォルトでは、複数行モードは無効になっています( ^$ は件名全体の開始と終了をマーク)。

e

サブマッチを抽出します。 REGEXP_INSTRREGEXP_SUBSTRREGEXP_SUBSTR_ALL、およびこれらの関数のエイリアスにのみ適用されます。

s

\n と一致する POSIX ワイルドカード文字 . を有効にします。デフォルトでは、ワイルドカード文字の一致は無効になっています。

デフォルトの文字列は単に c であり、次を指定します。

  • 大文字と小文字を区別する一致。

  • 単一行モード。

  • REGEXP_REPLACE を除き、常にサブマッチ抽出を使用するサブマッチ抽出はありません。

  • POSIX ワイルドカード文字 . は、 \n 改行文字と一致しません。

複数のパラメーターを指定する場合、文字列はスペースまたは区切り文字なしで入力されます。たとえば、 ims は、 POSIX ワイルドカード一致の複数行モードで大文字と小文字を区別しない一致を指定します。

パラメーター 文字列に ci の両方が含まれている場合は、文字列の最後により、関数が大文字と小文字を区別する一致を実行するかどうかが決まります。たとえば、 ci は、「i」が文字列の最後にあるため、大文字と小文字を区別する一致を指定します。

メタ文字である一致する文字

正規表現では、一部の文字は特定の意味を持つメタ文字として扱われます。例:

  • . は、 任意の1文字に一致するメタ文字 です。

  • * は、先行要素の0個以上のインスタンスに一致する 量指定子 です。たとえば、 BA*BBABAA などに一致します。

  • ? は、先行要素の0個または1個のインスタンスに一致する量指定子です。

実際の文字(例: 実際のピリオド、アスタリスク、疑問符)と一致させるには、メタ文字をバックスラッシュ(例: \.\*\?)でエスケープする必要があります。

注釈

一重引用符で囲まれた文字列定数 で正規表現を使用している場合は、2番目のバックスラッシュ(例: \\.\\*\\?)でバックスラッシュをエスケープする必要があります。詳細については、 一重引用符で囲まれた文字列定数での正規表現の指定 をご参照ください

たとえば、文字列内で開き括弧(()を見つける必要があるとします。これを指定する1つの方法は、バックスラッシュを使用してパターン内の文字をエスケープすることです(例: \()。

パターンを 一重引用符で囲まれた文字列定数 として指定する場合は、 そのバックスラッシュを2番目のバックスラッシュでエスケープ する必要もあります。

次のパターンは、括弧内に表示される英数字のシーケンスと一致します(例: (NY))。

SELECT REGEXP_SUBSTR('Customers - (NY)','\\([[:alnum:]]+\\)') as customers;

  +-----------+
  | CUSTOMERS |
  |-----------|
  | (NY)      |
  +-----------+

その他の例については、 一重引用符で囲まれた文字列定数でメタ文字を使用する例 をご参照ください。

ドル引用符で囲まれた文字列定数 を使用している場合は、バックスラッシュをエスケープする必要がないことに注意してください。

SELECT REGEXP_SUBSTR('Customers - (NY)',$$\([[:alnum:]]+\)$$) as customers;

  +-----------+
  | CUSTOMERS |
  |-----------|
  | (NY)      |
  +-----------+

後方参照の使用

Snowflakeは、正規表現パターン(形式言語理論では「スクエア」として知られる)の後方参照をサポートしていません。ただし、後方参照は REGEXP_REPLACE 関数の置換文字列でサポートされています。

空のパターンの指定

ほとんどの正規表現関数では、空のパターン(つまり、 '')は何にも一致せず、空の件名にも一致しません。

例外は REGEXP_LIKE と、そのエイリアスである REGEXP および RLIKE です。パターンは両端で暗黙的に固定されているため、空のパターンは空のサブジェクトに一致します(つまり、 '' は自動的に '^$' になる)。

空のグループ(つまり、サブ式 ())は、サブジェクトの開始と終了を含む文字間のスペースに一致します。

ドル引用符で囲まれた文字列定数での正規表現の指定

文字列定数を使用して関数の正規表現を指定している場合は、 ドル引用符で囲まれた文字列定数 を使用して、 正規表現のバックスラッシュをエスケープ する必要をなくすことができます。(一重引用符で囲まれた文字列定数 を使用している場合は、バックスラッシュをエスケープする必要があります。)

ドル引用符で囲まれた文字列定数の内容は、常に文字通りに解釈されます。

たとえば、 メタ文字 をエスケープする場合は、バックスラッシュを1つ使用するだけで済みます。

SELECT w2
  FROM wildcards
  WHERE REGEXP_LIKE(w2, $$\?$$);

後方参照 を使用する場合は、バックスラッシュを1つ使用するだけで済みます。

SELECT w2, REGEXP_REPLACE(w2, '(.old)', $$very \1$$)
  FROM wildcards
  ORDER BY w2;

一重引用符で囲まれた文字列定数での正規表現の指定

一重引用符で囲まれた文字列定数 で正規表現を使用している場合は、 バックスラッシュシーケンス のバックスラッシュを2番目のバックスラッシュでエスケープする必要があります。

注釈

正規表現でバックスラッシュがエスケープされないようにするには、一重引用符で囲まれた文字列定数ではなく、 ドル引用符で囲まれた文字列定数 を使用できます。

例:

一重引用符で囲まれた文字列定数でメタ文字を使用する例

この例では、疑問符(?)を検索する正規表現で、エスケープシーケンスの一部としてバックスラッシュを使用します。

テーブルを作成し、1つの列に1つのバックスラッシュを含み、別の列に疑問符を含む行を挿入します。

create table wildcards (w varchar, w2 varchar);
insert into wildcards (w, w2) values ('\\', '?');

次の SELECT は、疑問符のリテラルを検索します。検索では正規表現を使用します。疑問符は正規表現のメタ文字であるため、検索では疑問符をエスケープしてリテラルとして扱う必要があります。バックスラッシュは文字列リテラルで表示されるため、バックスラッシュ自体もエスケープする必要があり、クエリは次のようになります。

select w2
    from wildcards
    where regexp_like(w2, '\\?');
+----+
| W2 |
|----|
| ?  |
+----+

次のクエリでは、正規表現が2つの文字(バックスラッシュエスケープ文字と疑問符)で構成されていることを分かりやすくしています。

select w2
    from wildcards
    where regexp_like(w2, '\\' || '?');
+----+
| W2 |
|----|
| ?  |
+----+

前の例では、エスケープ文字が 文字列リテラルの一部 であり、正規表現自体ではないため、追加のバックスラッシュが必要でした。次の SELECT ステートメントは、文字列リテラルを SQL コマンド文字列の一部として解析する必要がないため、文字列リテラルで必要な追加のエスケープ文字は必要ありません。

select w, w2, w || w2 as escape_sequence, w2
    from wildcards
    where regexp_like(w2, w || w2);
+---+----+-----------------+----+
| W | W2 | ESCAPE_SEQUENCE | W2 |
|---+----+-----------------+----|
| \ | ?  | \?              | ?  |
+---+----+-----------------+----+

一重引用符で囲まれた文字列定数で後方参照を使用する例

文字列リテラルで 後方参照 (例: \1)を使用する場合は、その後方参照の一部であるバックスラッシュをエスケープする必要があります。たとえば、 REGEXP_REPLACE の置換文字列リテラルで後方参照 \1 を指定するには、 \\1 を使用します。

次の例では、前に作成したテーブルを使用しています。SELECT は、後方参照を使用して、正規表現 .old の各出現箇所を、「very」という単語が前に付いている一致した文字列のコピーに置き換えます。

insert into wildcards (w, w2) values (NULL, 'When I am cold, I am bold.');
select w2, regexp_replace(w2, '(.old)', 'very \\1')
    from wildcards
    order by w2;
+----------------------------+------------------------------------------+
| W2                         | REGEXP_REPLACE(W2, '(.OLD)', 'VERY \\1') |
|----------------------------+------------------------------------------|
| ?                          | ?                                        |
| When I am cold, I am bold. | When I am very cold, I am very bold.     |
+----------------------------+------------------------------------------+