CODEWITHSUNDEEP
postgresql
user2751391
user2751391

Reputation: 299

How to replace substring in Postgres

I want to replace substrings in PostgreSQL. For example string "ABC_dog" , 'dogABCcat', 'dogABC' to 'XYZ_dog', 'dogXYZcat', 'dogXYZ'

I tried:

UPDATE my_table SET name =  regexp_replace( name , '.*ABC.*', '.*XYZ.*', 'g')

but it set new names to '.XYZ.'

Upvotes: 3

Views: 3811

Answers (2)

Patrick
Patrick

Reputation: 32159

The simplest solution would be to use the replace() function:

UPDATE my_table SET name = replace(name , 'ABC', 'XYZ');

Keep in mind, though, that this will replace all rows in your table. Unless most rows have the pattern you want to replace, you are better off testing for the offending sub-string first:

UPDATE my_table SET name = replace(name , 'ABC', 'XYZ')
WHERE position('ABC' in name) > 0;

Upvotes: 7

Gary - Stand with Ukraine
Gary - Stand with Ukraine

Reputation: 7541

The pattern '.*' matches everything, so '.ABC.' means match everything before the ABC, the ABC and everything after as well, so effectively the whole string.

Change it to be just ABC as that is the bit you want to replace. Also, remove the .* from the replacement.

UPDATE my_table SET name =  regexp_replace( name , 'ABC', 'XYZ', 'g')

Upvotes: 1

Related Questions