How do you select all the rows that contain uppercase characters only? There sre three ways to do this

1 Compare with BINARY_CHECKSUM

2 Use COLLATE

3 Cast to varbinary

Let’s first create the table and also some test data

CREATE TABLE #tmp ( x VARCHAR(10) NOT NULL )

INSERT INTO #tmp 
SELECT 'Word' UNION ALL 
SELECT 'WORD' UNION ALL 
SELECT 'ABC' UNION ALL 
SELECT 'AbC' UNION ALL 
SELECT 'ZxZ' UNION ALL 
SELECT 'ZZZ' UNION ALL 
SELECT 'word' 

if we want only the uppercase columns then this is supposed to be our output

WORD

ABC

ZZZ

Let’s get started, first up is BINARY_CHECKSUM

SELECT x 
FROM #TMP 
WHERE BINARY_CHECKSUM(x) = BINARY_CHECKSUM(UPPER(x)) 

Second is COLLATE

SELECT x 
FROM #TMP 
WHERE x = UPPER(x) COLLATE SQL_Latin1_General_CP1_CS_AS 

Third is Cast to varbinary

SELECT x 
FROM #TMP 
WHERE CAST(x AS VARBINARY(10)) = CAST(UPPER(x) AS VARBINARY(10)) 

Of course if you database is already case sensitive you can just do the following

SELECT x 
FROM #TMP 
WHERE UPPER(x) = x 

That will work, how do you find out what collation was used when your database was created? You can use DATABASEPROPERTYEX for that. I use the model DB here because when you create a new DB by default it inherits all the properties from the model DB.

When I run this

SELECT DATABASEPROPERTYEX( 'model' , 'collation' ) 

I get this as output: SQL_Latin1_General_CP1_CI_AS

What does all that junk mean? Well let’s run the following function (yes those are 2 colons :: )

SELECT * 
FROM ::fn_helpcollations () 
WHERE NAME ='SQL_Latin1_General_CP1_CI_AS' 

The description column contains this info

Latin1-General, case-insensitive, accent-sensitive,

kanatype-insensitive, width-insensitive for Unicode Data,

SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

You can read some more info about Selecting a SQL Collation here: http://msdn2.microsoft.com/en-us/library/aa176552(SQL.80).aspx