Remove/replace special characters in column values?

Question

I have a table column containing values which I would like to remove all the hyphens from. The values may contain more than one hyphen and vary in length.

Example: for all values I would like to replace 123 - ABCD - efghi with 123ABCDefghi.

What is the easiest way to remove all hyphens & update all column values in the table?

Answer 1

You can use the regexp_replace function to left only the digits and letters, like this:

update mytable
   set myfield = regexp_replace(myfield, '[^\w]+','');

Which means that everything that is not a digit or a letter or an underline will be replaced by nothing (that includes -, space, dot, comma, etc).

If you want to also include the _ to be replaced (\w will leave it) you can change the regex to [^\w]+|_.

Or if you want to be strict with the characters that must be removed you use: [- ]+ in this case here a dash and a space.

Also as suggested by Luiz Signorelly you can use to replace all occurrences:

    update mytable
       set myfield = regexp_replace(myfield, '[^\w]+','','g');

Answer 2

You can use this.

update table set column = format('%s%s', left(column, 3), right(column, -6));

Before:

After: