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)