CODEWITHSUNDEEP
regexgoogle-bigquery
Mark Ginsburg
Mark Ginsburg

Reputation: 2269

Google BigQuery possible to do Case-Insensitive REGEXP_Match?

In Google BigQuery I wanted to check for 'confirm' or 'Confirm':

REGEXP_CONTAINS(h.page.PagePath, r'Confirm') or
REGEXP_CONTAINS(h.page.PagePath, r'confirm'))

I am a Perl person and in Perl we do

$foo =~ /confirm/i    # case-insensitive

Does Google BigQuery have any flags to modify REGEXP_MATCH? I did not see any examples in their online docs.

Upvotes: 14

Views: 18628

Answers (2)

Bob Frommer
Bob Frommer

Reputation: 9

Not as elegant, but I just used this:

REGEXP_CONTAINS(UPPER(sources), "CLAIMS")

Upvotes: -1

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626903

REGEXP_CONTAINS uses RE2 library, so you may use inline modifiers like this:

REGEXP_CONTAINS(h.page.PagePath, r'(?i)confirm') 
                                   ^^^^

See RE2 docs:

(?flags)    set flags within current group; non-capturing ...
                                                                Flags
i  case-insensitive (default false)
m  multi-line mode: ^ and $ match begin/end line in addition to begin/end text (default false)
s  let . match \n (default false)
U  ungreedy: swap meaning of x* and x*?, x+ and x+?, etc (default false)

Flag syntax is xyz (set) or -xyz (clear) or xy-z (set xy, clear z).

Upvotes: 36

Related Questions