Differentiation between "AB" and "Ab" in the character base field

Question

In particular, Sql Server 2005 / T-Sql. I have a field, basically it is a series of two characters, and they should all be in upper case, but there is some outdated data that precedes the current database / system, and I need to find out which records violate the upper binding of the covenant.

I thought this would work:

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

And indeed, it returned me a few notes. Since then, they have been fixed, and now this query does not return data. But I have people who tell me that there is still mixed data in the database, and I just found an example: “FS” and “Fs” report the same ascii value.

Why is this approach wrong? What is better for this, or how can I fix this approach to work properly?

Answer 1

if the whole date was supposed to be uppercase, just do the update

update tbl
set field1 = upper(field1)

but in order to answer your initial question, this query should give you the expected results:

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

Edit: Just noticed that the proposal to use COLLATE was also published by Ian

Answer 2

ASCII compares only the first letter. You will need to compare each letter or change the sorting of the database case-sensitive.

You can change the sorting at the whole database level or only by one column for a specific query, therefore:

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

Answer 3

ascii() ascii , . , , , .

Answer 4

ASCII() ASCII . UPPER().

Answer 5

:

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

Answer 6

, SQL Server .

Answer 7

ASCII(), .

, . :

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

, ? , :

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