How to replace numbers in SQL

Question

My database has a lot of descriptions which are similar to each other and I want to group them together, but because of different numbers, they are not being grouped together. So is there any way I can mask the numbers and make the descriptions same.

We can do that in Excel or Notepad++, using find and replace, so anyway in which it would be possible in SQL. I know we can replace in SQL using the function

REPLACE('column', 'new input', 'to be replaced')

But how to do it for regex, as the numbers can be in any combination.

I am using PostgreSQL.

Some inputs :-

sample input description 123
sample input description 456
this is another description 678
this is another description 999

I would like to convert them to:-

sample input description xxx
sample input description xxx
this is another description xxx
this is another description xxx

the numbers can be anywhere.

I am doing it on redshift.

Solution 1

You can use this REGEXP_REPLACE function in below format.

select regexp_replace ( columnthatneedtomask,'[0-9]','x' )  from table ;

refer below link for more information:-

https://docs.aws.amazon.com/redshift/latest/dg/REGEXP_REPLACE.html

Solution 2

You'd use

regexp_replace(col, '[[:digit:]]+', '#')

in order to replace any number of digits with a single #.

Rextester demo: http://rextester.com/BFSP36237

Use the flag 'g' if multiple numbers can occur in a string:

regexp_replace(col, '[[:digit:]]+', '#', 'g')

Rextester demo: http://rextester.com/WHTJ51233

Solution 3

select regexp_replace(column, '[0-9]', 'x', 'g') as newcolumn from table;