Google BigQuery Replace function for string type

Question

I am trying to replace certain customer names in my data. I was able to do SQL using Google BigQuery language to transform one part of the string another via the replace function for one particular string.

Replace(CustomerName, 'ABC', 'XYZ')

However, I have a couple more that I would need to use the replace function such that

Replace(CustomerName, 'PLO', 'Rustic')
Replace(CustomerName, 'Kix', 'BowWow')

and so on.

I've tried doing

Replace(CustomerName, 'ABC', 'XYZ') OR Replace(CustomerName, 'PLO', 'Rustic') OR Replace(CustomerName, 'Kix', 'BowWow')

but that got me an error message.

I've also tried

Replace(CustomerName, 'ABC', 'XYZ') AND Replace(CustomerName, 'PLO', 'Rustic') AND Replace(CustomerName, 'Kix', 'BowWow')

but that also got me an error message.

I am able to just use "case when statement" and then hardcode each one, but I'm wondering if there is a better/faster way to just use replace statement instead.

Thanks for your help.

Answer 1

The CASE WHEN option is pretty reasonable. Another option is to chain them together:

REPLACE(
  REPLACE(
    REPLACE(
      CustomerName,
      'ABC',
      'XYZ'),
    'PLO',
    'Rustic'),
  'Kix',
  'BowWow')

Which one you pick really depends on the exact scenario. The chained REPLACE calls are probably faster, but they could overlap in weird ways (e.g., if the output to one replacement matches the input to a subsequent one). The CASE WHEN approach avoids that issue, but it's probably more expensive because you need to do one operation to find the substring and another to actually replace it.

Note that when you're using AND or OR, you're trying to combine the string output of REPLACE as if it were a boolean, which is why it's failing.