Data Studio
2.4K members online now
2.4K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Use REGEX to create simple if statement

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi there, 

 

I'm trying to create a calculated field that returns a certain value if another field begins with a string, or return something else if it doesn't. 

 

I'm much more familiar with Microsoft Access than Regex, and would use this expression if working in MS Access:

 

IIf ( [Product] = "Compatible*" , Compatible , Genuine )

 

Pretty simple stuff. I want to look for all products that begin with the word "Compatible" and if true return "Compatible" in this field. If not true then return "Genuine". 

 

So in Data Studio I've come up with the following Regex (which is not working):

 

CASE WHEN Product = '^Compatible' THEN 'Compatible' ELSE 'Genuine' END

 

What I'm trying to do is pretty simple but I can't get it to work. 

 

Would someone be able to please correct me so I can get this to work. 

1 ACCEPTED SOLUTION

Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Scott D
June

Use REGEX to create simple if statement

Follower ✭ ✭ ☆
# 4
Follower ✭ ✭ ☆

I think Mimoune meant use the "REGEXP_MATCH" formula so something like:

 

CASE WHEN REGEXP_MATCH(Product, '^Compatible.*') THEN 'Compatible' ELSE 'Genuine' END

 

View solution in original post

Use REGEX to create simple if statement

Explorer ✭ ✭ ☆
# 2
Explorer ✭ ✭ ☆

Use REGEX to create simple if statement

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hey Mimoune, 

 

That question is similar but not the same. I'm not looking for the entire field to match, only the beginning of the string. 

 

So if Product is Compatible Brother TN2345 Toner Cartridge... it will recognise that it begins with "Compatible". 

 

From that post I tried:

 

CASE WHEN Product = '^Compatible.*' THEN 'Compatible' ELSE 'Genuine' END

 

... but that doesn't work either.  

Marked as Best Answer.
Solution
Accepted by topic author Scott D
June

Use REGEX to create simple if statement

Follower ✭ ✭ ☆
# 4
Follower ✭ ✭ ☆

I think Mimoune meant use the "REGEXP_MATCH" formula so something like:

 

CASE WHEN REGEXP_MATCH(Product, '^Compatible.*') THEN 'Compatible' ELSE 'Genuine' END

 

Use REGEX to create simple if statement

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Thanks Robert, you got it exactly right. That worked. 

 

Thanks very much.