how to replace all numbers from a string using sql

Question

i'm trying to return a column with every instance of a number replaced by '!'.

the following code only replaces the first instance from each row:

select project, commits, contributors, regexp_replace(address, '[0-9]', '!') as address
from repositories

1BcJBCAYqW9 should return as so !BcJBCAYqW! but the output I get is !BcJBCAYqW9, where the second digit does not change.

Gordon Linoff · Accepted Answer · 2019-08-08 20:53:02Z

1

The code works as expected in MySQL and Oracle. So, I am going to guess you are using Postgres or a Postgres-derived database.

Postgres requires an additional argument to specify that all occurrences should be replaced:

select regexp_replace(address, '[0-9]', '!', 'g') as address
from (select '1BcJBCAYqW9' as address ) x

answered Aug 8, 2019 at 20:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Answer 1