Why the replace function in PostgreSQL is not returning the value?

Question

I am trying to convert state names to abbreviations and I am using the replace function. I am able to run the snippet successfully but names remain the same without being converted to abbreviation:

UPDATE county SET state_name = replace(state_name, '(Texas)', 'TX');
UPDATE county SET state_name = replace(state_name, '(Utah)', 'UT');
UPDATE county SET state_name = replace(state_name, '(Vermont)', 'VT');
UPDATE county SET state_name = replace(state_name, '(Virginia)', 'VA');
UPDATE county SET state_name = replace(state_name, '(Washington)', 'WA');
UPDATE county SET state_name = replace(state_name, '(West Virginia)', 'WV');
UPDATE county SET state_name = replace(state_name, '(Wisconsin)', 'WI');
UPDATE county SET state_name = replace(state_name, '(Wyoming)', 'WY');
UPDATE county SET state_name = replace(state_name, '(Puerto Rico)', 'PR');

--Let's check the result
select * from county;

Answer 1

OP based on your image, your replace() function is trying to match something like..

Puerto Rico ..which is the value in the database, with..

(Puerto Rico) ..which is the value you're telling it to match in the second argument of your replace() function.

Remove the ( and the ) from the second argument in your replace() calls, so they look like this..

REPLACE(state_name, 'Puerto Rico', 'PR')

..and I expect it will work. Basically the replace() function is working, it just isn't happening because the patterns it's looking for don't exist to match against.

Don't over look the potential for "dirty data".

In some cases—many cases—and apparently in this case, based on the comments, data values are not as clean as we would like and we need to condition them a little for the patterns to match as expected. Frequently, whitespace like spaces, tabs, and even carriage returns are the culprit, because at a glance we cannot perceive them. Secondly, character case (i.e. lower case, upper case) can prevent string value matching. So knowing this, if a seemingly obvious pattern is not matching as we might expect, the low-hanging fruit is to remove white space and eliminate character case mismatches. The following will remove leading and trailing spaces in the record data and force everything to upper case prior to testing the replace pattern supplied:

REPLACE(LTRIM(RTRIM(UPPER(state_name))), UPPER('Puerto Rico'), 'PR')

Note that this will not catch tabs or carriage returns! If you have more complex whitespace, you'll need to do a little more research. Start with this answer to a similar question over at dba.stack or perhaps the discussion noted here under the heading "Removing whitespace characters from a string".