How to replace substring in Postgres

Question

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.'

Answer 1

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;