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

The ascii() function will only return the ascii number for the first character in an expression if you pass it a multiple character string. To do the comparison you want you need to look at individual characters, not entire fields.

Answer 2

The ASCII() function returns only the ASCII code value of the leftmost character of a character expression. Use UPPER() instead.

Answer 3

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 4

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)

Answer 5

This might work:

select * from tbl 
where cast(field1 as varbinary(256)) <> cast(upper(field1) as varbinary(256))

Answer 6

The methods described at Case sensitive search in SQL Server queries might be useful to you.

Answer 7

According to the documentation for ASCII(), it only returns the leftmost character.

I think you're going about this wrong. You could simply:

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

if the collation rules were set correctly, so why not fix the collation rules? If you can't change them permanently, try:

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