SQL: Remove part of string that is in another column of the table using REGEXP_REPLACE

I have a column that looks like so, but with more rows:

| CatName  | Message                                      | 
| Garfield | You are a great cat Garfield                 | 
| Goofy    | I do not like Goofy, he is a mediocre cat    | 
| RoseMary | You are a great cat RoseMary                 | 
| Joe      | Joe, you are like a ferocious lion!          |
| Astrid   | Cat food is tasty                            |

Now, I am trying to remove the cat name from the message on each row in the table, so that I can see what the messages sent to each cat are, and which cats got the same message (i.e. this way I would be able to see that Garfield and RoseMary got identical messages).

SELECT 
  Message,
  CatName,
  REGEXP_REPLACE(Message, '^' || CatName, '') msg_no_name
FROM cat_table

I attempted to solve this with regexp_replace (where I want to replace the CatName part of each Message with nothing — but I think I am getting it wrong*. Anyone who can see where I err, or have a better suggestion for how I solve this issue?

BigQuery throws this error: Cannot parse regular expression: bad repetition operator: ???

Answer

Why not just use replace()?

select replace(message, catname, '')

Advertisement

Answer