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.

That's not masking. You want to transform the input based on some unspecified rules that will produce the same result from different input. What are those rules? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 18, 2018 at 9:43
Postgresql supports regex contrary to sql-server. Regex .Net assembly can be accesed from sql-server using CLR integration. — Serg
– Serg, Commented Jan 18, 2018 at 9:46
@Serg and neither should be used carelessly. A query that uses a regex can't use any underlying indexes. The server would have to cache the transformed data before grouping. It's even worse for filtering - the server would have to scan the entire table and consume the entire text value each time before determining whether there's a match or not. This can waste a lot of memory if there are many rows and/or the text field is large — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 18, 2018 at 9:51

Thorsten Kettner · Accepted Answer · 2018-01-18 10:19:07Z

4

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

edited Jan 18, 2018 at 10:19

answered Jan 18, 2018 at 10:10

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aklank Jain · Accepted Answer · 2018-01-18 11:08:32Z

2

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

edited Jan 18, 2018 at 11:08

Aklank Jain

1,0721 gold badge15 silver badges23 bronze badges

answered Jan 18, 2018 at 10:03

brajkishore dubey

866 bronze badges

Comments

Simas Joneliunas · Accepted Answer · 2021-09-20 23:36:52Z

2

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

edited Sep 20, 2021 at 23:36

Simas Joneliunas

3,15620 gold badges32 silver badges39 bronze badges

answered Sep 20, 2021 at 22:35

Luciana Wakabayashi

411 bronze badge

1 Comment

Alex Yu Over a year ago

It would be better if you provide some explanations, maybe a link to documentation. And why from table?

Collectives™ on Stack Overflow

How to replace numbers in SQL

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related