Force T-SQL query to be case sensitive in MS

sql-server tsql case-sensitive
Twelfth picture Twelfth · Oct 4, 2010 · Viewed 13.1k times · Source

I have a table that originates in an old legacy system that was case senstive, in particular a status column where 's' = 'Schedule import' and 'S' = 'Schedule management'. This table eventually makes its way into a SQL Server 2000 database which I can query against. My query is relatively simple just going for counts...

Select trans_type, count(1) from mytable group by trans_type

This is grouping the counts for 'S' along with the 's' counts. Is there any way to force a query to be cap sensitive? I have access to both SQL Server 2000 and 2005 environments to run this, however have limited admin capability on the server (so I can't set server attributes)... I guess I could move the data to my local and setup something on my local where I have full access to server options, but would prefer a tsql solution.

Answer

Ian Henry picture Ian Henry · Oct 4, 2010 
select trans_type collate SQL_Latin1_General_CP1_CS_AS, count(*)
from mytable
group by trans_type collate SQL_Latin1_General_CP1_CS_AS

You can do this with =, like, and other operators as well. Note that you must modify the select list because you are no longer grouping by trans_type, you are now grouping by trans_type collate SQL_Latin1_General_CP1_CS_AS. Kind of a gotcha.

Related questions

SQL Server check case-sensitivity?

How can I check to see if a database in SQL Server is case-sensitive? I have previously been running the query: SELECT CASE WHEN 'A' = 'a' THEN 'NOT CASE SENSITIVE' ELSE 'CASE SENSITIVE' END But I am looking for other …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More