Upper and Lower case in SQL SERVER

Working in SQL SERVER 2012 (meaning, I haven’t tried this in other version),  I found that a huge table of gis data had 49 rows with lower case postal codes.

As the data I would be using to find these would be in upper case,  I chose to convert these to upper case instead of doing it in the query,  because that would increase the resources to run said query (and I would forget to do that at some point down the road).

To do this,  I used the SQL Server functions UPPER and LOWER.

To find columns that are either in all upper or lower case,  you simply compare the contents of the column to what the UPPER or LOWER function returns.

To find the lower case codes,  this was the query I used


SELECT
*
FROM
geolocation
WHERE
code = LOWER(code)       //this is the bit that finds the lower case fields

As I ran through the steps to convert these to upper case,  I initially used the condition

code = LOWER(code)

in the UPDATE statement.

To my chagrin, there were still 2 rows that weren’t updated.

Both of them had an uppercase character.

To get around this,  I changed the condition to

code != UPPER(code)

which worked fine.

The lesson here is that when you are converting a column to a desired case,  use the target case as one of your conditions.

…which makes perfect sense when I type that out loud.

Here’s the code


UPDATE
geolocation
SET
code = UPPER(code)
WHERE
code= UPPER(code)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s