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

SQL SERVER: Conditional selects

In a recent project I needed to come up with a way to return information from one of two tables, based on whether or not the value of the first column was null.

In situations like this,  the SELECT CASE statement is the best option.

This is the syntax:


(

SELECT CASE WHEN

[condition]

THEN

[return if true]

ELSE

[return if false]

END

)

Its important to know that you can use SELECT CASE in both the selects AND the WHERE CLAUSES of a sql statement.

Here is a full query,  where,  if the person’s address is null in table a,  it will provide the address from table b.

You would use the same syntax in the WHERE CLAUSE.


SELECT

a.name as Name,

(

SELECT CASE

WHEN

a.address IS NULL

THEN

b.address

ELSE

a.address

END

) as Address

FROM

users AS a

INNER JOIN

userdata AS b ON a.UID = b.UID