-1

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;

enter image description here

  • Please remember to always place code as text in a code block within the body of the question. This makes it legible on all devices and permits the volunteers who would help you to try their own solutions without each first copying the text from an image. It is unclear that this is a GIS question. It may be more appropriate in Database Administrators. – Vince May 8 at 16:16
  • 1
    Thank you for the comment. I am going to put the code as text and change the tag – Milad May 8 at 16:28
  • what does select replace('flimflam (Texas) blahblah', '(Texas)', 'TX') as state return? – Paul H May 8 at 16:38
  • 1
    @PaulH that returns "flimflam TX blahblah", so I guess we need to know what is in state_name – Ian Turton May 8 at 16:56
  • I agree with @IanTurton....I'm betting state_name has leading and/or trailing whitespace, or it's in capital letters, or for whatever reason the pattern is just not what OP is expecting. ....hopefully, whatever it is, it's systematic! – elrobis May 8 at 17:03
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".

  • Thank you so much! – Milad May 8 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.