Zenn
🦉

BigQueryの正規表現で大文字・小文字を区別せずに文字列を検索

2024/11/03に公開
SQL
BigQuery
regex
regexp
ETL
tech

結論

REGEXP_CONTAINS(text, r"(?i)hoge")

理由

BigQueryはre2を正規表現エンジンとして使っているから。

参照: BigQuery String Functions

Note: GoogleSQL provides regular expression support using the re2 library; see that documentation for its regular expression syntax.

https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#regexp_contains

モチベーション

  • 表題をBigQueryのワークフローやクエリで実現する
  • Perl互換の正規表現で書かれた正規表現をBigQueryに書き直す

なお、Perl互換正規表現（Perl Compatible Regular Expressions, PCRE）では以下のような表現になる。

/hoge/i

テスト

テストコードを記述し、実際の挙動を確認。

テストコード

WITH 
test_cases AS (
  SELECT "hello" AS text, TRUE AS expected UNION ALL
  SELECT "world", TRUE UNION ALL
  SELECT "Hello", TRUE UNION ALL
  SELECT "WORLD", TRUE UNION ALL
  SELECT "hello i am shunsock!!", TRUE UNION ALL
  SELECT "the world will be better", TRUE UNION ALL
  SELECT "Nothing to match here", FALSE
),

results AS (
  SELECT 
    text,
    expected,
    REGEXP_CONTAINS(text, r"(?i)hello|world") AS actual
  FROM 
    test_cases
)

SELECT 
  text,
  expected,
  actual,
  CASE
    WHEN expected = actual THEN "Test Passed"
    ELSE "Test Failed"
  END AS test_result
FROM 
  results

テスト結果

参照先

re2の文法

文法がまとまっているGitHubのページ。基本はこのページを参照すればよい。

https://github.com/google/re2/wiki/Syntax

re2について

re2そのものについてはREADME参照。色々な言語のwrapperが存在する。

https://github.com/google/re2/blob/main/README

Discussion