Premjit Mohanty: How can I make my SQL queries case sensitive?

Thursday, 8 April 2010

How can I make my SQL queries case sensitive?

CREATE TABLE mytable 
( 
    mycolumn VARCHAR(10) 
) 
GO 
 
SET NOCOUNT ON 
 
INSERT mytable VALUES('Case') 
GO 
 
SELECT mycolumn FROM mytable WHERE mycolumn='Case' 
SELECT mycolumn FROM mytable WHERE mycolumn='caSE' 
SELECT mycolumn FROM mytable WHERE mycolumn='case'

You can alter your query by forcing collation at the column level:

SELECT myColumn FROM myTable  
    WHERE myColumn COLLATE Latin1_General_CS_AS = 'caSE' 
 
SELECT myColumn FROM myTable  
    WHERE myColumn COLLATE Latin1_General_CS_AS = 'case' 
 
SELECT myColumn FROM myTable  
    WHERE myColumn COLLATE Latin1_General_CS_AS = 'Case' 
 
-- if myColumn has an index, you will likely benefit by adding 
-- AND myColumn = 'case'