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?

1

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');
5

You can use this.

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

Before:

After:

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.