Differentiating between "AB" and "Ab" in a character Database Field

Question

Specifically, Sql Server 2005/T-Sql. I have a field that is mostly a series of two characters, and they're all supposed to be upper case but there's some legacy data that predates the current DB/System, and I need to figure out which records are in violation of the upper casing covenant.

I thought this would work:

select * from tbl where ascii(field1) <> ascii(upper(field1))

And indeed it returned me a handful of records. They've since been corrected, and now that query returns no data. But I've got people telling me there is still mixed case data in the DB, and I just found an example: 'FS' and 'Fs' are both reporting the same ascii value.

Why is this approach flawed? What is a better way to go about this, or how can I fix this approach to work correctly?

Answer 1

if all the date should have been in upper case just do an update

update tbl
set field1 = upper(field1)

but to answer your original question this query should give you the results that you expect:

select * from tbl
where field1 COLLATE Latin1_General_CS_AS <> upper(field1)

Edit: just noticed that the suggestion to use COLLATE was also posted by Ian

Answer 2

ASCII is only comparing the first letter. You'd have to compare each letter, or change the database collation to be case sensitive.

You can change collation on an entire database level, or just on one column for a specific query, so:

SELECT myColumn 
  FROM myTable  
  WHERE myColumn COLLATE Latin1_General_CS_AS <> upper(myColumn)