CODEWITHSUNDEEP
sqlgoogle-bigquery
user3447653
user3447653

Reputation: 4158

Regular expression in google big query

Query:

SELECT DebugData
CASE
 WHEN lower(DebugData) LIKE 'veri%' THEN 'Verizon'
 WHEN REGEXP_MATCH(LOWER(DebugData),r'^\d+$') THEN c.Network 
 ELSE REGEXP_REPLACE(lower(DebugData),r'[^a-zA-Z]', ' ')
END
AS ActualCarrier
From table

Data:

DebugData  Correct_result  Current_result
310410?                    c.Network
302220?                    c.Network
Comcel2                    Comcel
Gig?                       Gig

Currently the query does not work for first 2 cases. I am sure that nothing to do with c.Network, because I also tried the statement below and it does not work.

WHEN REGEXP_MATCH(LOWER(DebugData),r'^\d+$') THEN 'hi'

Any help would be appreciated.

Upvotes: 1

Views: 10975

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173191

Meantime, try below - I think it captures that missing part of your requirements 

SELECT DebugData
CASE
 WHEN lower(DebugData) LIKE 'veri%' THEN 'Verizon'
 WHEN REGEXP_MATCH(LOWER(DebugData),r'^\d+[^a-zA-Z]*$') THEN c.Network 
 ELSE REGEXP_REPLACE(lower(DebugData),r'[^a-zA-Z]', ' ')
END
AS ActualCarrier
From table

Note: You should finalize your requirements at least for yourself and try to adopt above to your particular case

I would recommend to read Regular expression functions and RE2 regular expression syntax reference

Upvotes: 4

Related Questions