how to detect mixed case (SQL can be so insensitive)

So I was trying to find cities in an address table with mixed case names (like, “Seattle” instead of “SEATTLE”, etc). I was completely stumped until my search turned up the Collate clause. Which I think I used once last year, and then forgot about.

select * from tableX

where fieldY not like upper(fieldY) COLLATE SQL_Latin1_General_CP1_CS_AS

and fieldY not like lower(fieldY) COLLATE SQL_Latin1_General_CP1_CS_AS

The logic could also be switched around to detect all upper case or all lower case conditions. Getting a little fancy with substring, you could also look for terms that begin with a capital letter or some other pattern.

Note: COLLATE has to come after each condition that it applies to.

Advertisement