Fabric Data Warehouse で正則表現関数を使う

適用対象:✅ Microsoft Fabric の SQL 分析エンドポイントおよびウェアハウス

正則表現(regex)関数は複雑なテキストパターンをマッチングし、T-SQLクエリで文字列データを直接操作することを可能にします。 フォーマットの検証、半構造化テキストからの値抽出、パターンの発生数のカウント、データをウェアハウスから移動せずにクリーンまたはマスキングするために使えます。

Note

正規表現は現在プレビュー段階です。

ウェアハウスとSQL分析エンドポイントは スカラー 正規表現関数をサポートしています。 これらの関数は入力行ごとに 1 つの値を返すため、SELECT 句のリスト、WHERE、JOIN、GROUP BY、ORDER BY、CASE 式で使用できます。

Note

ウェアハウスにはスカラー正規表現関数のみが利用可能です。 テーブル値関数 REGEXP_MATCHES と REGEXP_SPLIT_TO_TABLE はサポートされていません。

サポートされている関数

機能 返り値の種類 Description
REGEXP_LIKE ブール値 テキスト入力が正則表現パターンに合致しているかどうかを示します。 述語として使用します。例えば、WHERE節やCASE式で使います。
REGEXP_REPLACE 文字列式 元の文字列内の正則表現パターンの出現箇所を置換文字列で置き換えた文字列を返します。 一致が見つからない場合は、元の文字列を返します。
REGEXP_SUBSTR 文字列式 正則表現パターンに一致する部分文字列の n回目の出現を返します。
REGEXP_INSTR int マッチングされた部分文字列の開始または終了位置を返します。
REGEXP_COUNT int 文字列内で正則表現パターンが現れる回数を返します。

Syntax

REGEXP_LIKE ( string_expression, pattern_expression [ , flags ] )

REGEXP_REPLACE ( string_expression, pattern_expression [ , string_replacement [ , start [ , occurrence [ , flags ] ] ] ] )

REGEXP_SUBSTR ( string_expression, pattern_expression [ , start [ , occurrence [ , flags [ , group ] ] ] ] )

REGEXP_INSTR ( string_expression, pattern_expression [ , start [ , occurrence [ , return_option [ , flags [ , group ] ] ] ] ] )

REGEXP_COUNT ( string_expression, pattern_expression [ , start [ , flags ] ] )

各引数の詳細な説明については、「対応関数」内のリンクされた関数リファレンスを参照してください。

フラグ

オプションの flags 引数は、最大30文字の char または varchar 値で、照合修飾子を設定します。 矛盾するフラグを出すと、最後のフラグが勝ちます。 例えば、'ic' は大文字と小文字を区別するマッチングになります。

Flag Description
c 大文字と小文字が区別される照合。 これが既定値です。
i 大文字と小文字を区別しない一致。
m マルチラインモード: ^ と $ は各行の始まりと終わり、さらにテキストの始まりと終わりも一致します。
s .を\n改行文字に一致させます。

Considerations

  • string_expressionの引数は文字列型を受け入れます。 大きな値(varchar(max))は最大2 MBまでサポートされます。
  • pattern_expression引数は最大8,000バイトをサポートします。
  • 正規表現関数は照合ルールを尊重しません。 マッチングは LIKEなど他の文字列比較と異なる場合があります。 大文字と小文字の区別を制御するには、列の照合に頼らず、 i と c フラグを使いましょう。 詳細については、「 データ ウェアハウスの照合順序」を参照してください。
  • REGEXP_LIKE はブール値を返し、列として直接返すことはできません。 結果を列として返すには、 CASE 式でラップします。

Examples

以下の例は小さなインラインデータセットを使用しているため、どのウェアハウスやSQL分析エンドポイントでも実行可能です。

A. パターンに一致する行をフィルターする

WHERE節で REGEXP_LIKE を使って、有効なメールアドレス形式の行のみを返してください。

SELECT CustomerId, Email
FROM (VALUES
        (1, 'anna@contoso.com'),
        (2, 'not-an-email'),
        (3, 'Bob.Smith@Fabrikam.org')
     ) AS c (CustomerId, Email)
WHERE REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

マッチ結果を列として返すには、REGEXP_LIKE を CASE 式で囲みます:

SELECT Email,
       CASE WHEN REGEXP_LIKE(Email, '@contoso\.com$', 'i') THEN 'Internal' ELSE 'External' END AS EmailType
FROM (VALUES ('anna@CONTOSO.com'), ('bob@fabrikam.org')) AS c (Email);

B. テキストから値を抽出する

REGEXP_SUBSTRを使って、フリーテキストコメントからORD-に続いて数字が並ぶ形式の最初の注文番号を抽出します。

SELECT Comment,
       REGEXP_SUBSTR(Comment, 'ORD-\d+') AS OrderNumber
FROM (VALUES
        ('Customer called about ORD-10293 and ORD-10311.'),
        ('No order referenced.')
     ) AS t (Comment);

マッチの一部だけを抽出するには、キャプチャーグループを使いましょう。 以下のクエリは各メールアドレスのドメインを返します:

SELECT REGEXP_SUBSTR(Email, '@(.+)$', 1, 1, 'c', 1) AS EmailDomain
FROM (VALUES ('anna@contoso.com'), ('bob@fabrikam.org')) AS c (Email);

C. パターンの出現回数を数える

REGEXP_COUNTを使って、各文字列にいくつの数字が入っているか数えます。

SELECT Comment,
       REGEXP_COUNT(Comment, '\d+') AS NumberCount
FROM (VALUES
        ('Shipped 3 boxes on 12 May, invoice 4471.'),
        ('No numbers here.')
     ) AS t (Comment);

D. マッチの位置を見つける

REGEXP_INSTRを使って、最初の数字列の開始位置と終了位置を見つけます。

SELECT ProductCode,
       REGEXP_INSTR(ProductCode, '\d+')             AS FirstDigitStart,
       REGEXP_INSTR(ProductCode, '\d+', 1, 1, 1)    AS FirstDigitEnd
FROM (VALUES ('BK-R93R-62'), ('HL-U509')) AS p (ProductCode);

E. データをクリーンアップしてマスクする

REGEXP_REPLACEを使ってキャプチャグループを使って電話番号を標準化し、最後の4桁以外はすべてマスキングします。

SELECT Phone,
       REGEXP_REPLACE(Phone, '^(\d{3})-(\d{3})-(\d{4})$', '(\1) \2-\3') AS FormattedPhone,
       REGEXP_REPLACE(Phone, '^\d{3}-\d{3}', '***-***')               AS MaskedPhone
FROM (VALUES ('425-555-0187'), ('206-555-0142')) AS c (Phone);

以下のクエリは繰り返しの空白を1つのスペースにまとめます:

SELECT REGEXP_REPLACE('Fabric    Data   Warehouse', '\s+', ' ') AS CleanText;

F. 抽出値によるグループおよび集計

関数はスカラーなので、GROUP BY 句で使うことができます。 以下のクエリはメールドメインごとの顧客数をカウントしています。

SELECT LOWER(REGEXP_SUBSTR(Email, '@(.+)$', 1, 1, 'c', 1)) AS EmailDomain,
       COUNT(*) AS Customers
FROM (VALUES
        ('anna@contoso.com'),
        ('bob@Contoso.com'),
        ('chris@fabrikam.org')
     ) AS c (Email)
GROUP BY LOWER(REGEXP_SUBSTR(Email, '@(.+)$', 1, 1, 'c', 1));

関連するコンテンツ

  • 最終更新: