Search code examples
regexgoogle-bigqueryregexp-replace

Regexp_replace Bigquery match 'Jungle book, The' with 'The Jungle Book

I'm using REGEXP_REPLACE with bigquery. I'm asking whether there is a better way to use REGEXP_REPLACE.

My current column is determined as follows:

CASE WHEN UPPER(Title) CONTAINS(', THE') THEN CONCAT('THE ', regexp_replace(Title, r', THE\s*\z', r'')) ELSE UPPER(Title) end as title_s,

I want to replace "Jungle book, The" with "THE JUNGLE BOOK".

Can someone provde me with a better solution please.

Solution

  • You may try this:

    regexp_replace(UPPER(Title), r'(.*), THE\s*\z', r'THE \1')  as title_s,

    Which will Upper the title, then move the THE part if needed