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.'
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;
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')
REGEXP_REPLACE() at all if you're not going to use a regex to match a pattern. Just use plain REPLACE() instead.