REGEXP_MATCH in Data Studio

user_13268667 0
Viewed 170

I am currently using datastudio to transform my data into reporting and I had problems in creation because the data available is not very exploitable. I would like to clean them through the regexp functions but I can't find the right expression

Exemple : 

      1- Apple
      2- Apples
      3 - Pre-apple
      4- Pré-apples
      5-Prèapple

I'm looking to transform to 

      Apple
      Preapple

Can someone help me please? , thank you !

google-data-studio mysql sql
2 Answers

A CASE statement with a couple of REGEXP_MATCH functions does the trick:

CASE
  WHEN REGEXP_MATCH(Field, ".*(Pr[eèé]-?apples?).*") THEN "Preapple"
  WHEN REGEXP_MATCH(Field, ".*(Apples?).*") THEN "Apple"
  ELSE "Other"
END

Created a Google Data Studio Report to demonstrate:

user_128925530

It looks like you want everything after the first "- ". For this use instr() and substr():

select substr(col, instr(col, '- ') + 2)

Usually, in MySQL, the simplest solution is substring_index(). But you might have multiple '- ' and you only care about the first one. If you don't, then:

select substring_index(col, '- ', 2)
user_11440350
Related