2

I try to learn SQL and Regex at the moment to analyse data in google data studio. I tried to find a solution to my problem but I could not find any online. I have a data source with search queries and I want to tag all queries which are including the company name as "Branded". This Part is easy. I just wrote the following to get it done:

case
when REGEXP_MATCH(Query, '.*flnk.*') THEN 'Branded'
ELSE 'Non-branded'
END

But now I also want to mark as "Branded" all search queries that contain the company name but are misspelt. So I tried the following:

case
when REGEXP_MATCH(Query, '^[f].*[k]$') THEN 'Branded'
ELSE 'Non-branded'
END

But with this code, I get a lot of wrong tagged queries, because it tags all strings which start with f and are ending with k. So my question is how can I only tag words as branded which are starting with f and ending with k?

Thank you so much in advance!

Yannik
  • 21
  • 1

1 Answers1

1

You can consider using

case
when REGEXP_MATCH(Query, r'.*\bf[a-zA-Z]*k\b.*') THEN 'Branded'
ELSE 'Non-branded'
END

The .*\bf[a-zA-Z]*k\b.* pattern matches

  • .* - any text
  • \b - a word boundary
  • f - f letter
  • [a-zA-Z]* - zero or more letters
  • k - a k letter
  • \b - a word boundary
  • .* - any text
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563