How to replace special characters from a column using DUCK DB?

Question

I have below table in Duck Db. I want to replace the special characters in the column state with space. I used regex to do it but doesn't replace the special characters. What is the best way to replace the special characters in the columns using Regex?

Table :-

SQL Query:-

UPDATE test
SET State = REGEXP_REPLACE(State,'[^a-zA-Z0-9\s]', ' ')
WHERE State is not Null;

Output is same as the input.

Answer 1

By default, the regexp_replace function only replaces the first occurrence of the regular expression. Use the global replace flag, g, as the fourth argument of regexp_replace:

UPDATE test
SET State = regexp_replace(State,'[^a-zA-Z0-9\s]', ' ', 'g')
WHERE State IS NOT NULL;