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?
2 Answers
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');
You can use this.
update table
set column = format('%s%s', left(column, 3), right(column, -6));
Before:
After:
