SQL Duplicate query counter needing collation

Question

I need some help coming up with a better query.

I am trying to count and display the dups in a file based on a string but I need to apply collation so I can tell whether one word maybe capitalized and another not. For example 'exit' or 'Exit' are not the same.

I started with this query but can not figure out a good way to implement Collation.....

SELECT shrt_Txt, count(*)
FROM tblLangUS
GROUP BY shrt_Txt  
HAVING count(*) > 1   

Collation statement I need to apply... COLLATE Latin1_General_CS_AS

anyone have a better way to do this? SQL Server 2008 R2 Express.

Answer 1

Try this:

SELECT shrt_Txt, count(*)
FROM (SELECT shrt_Txt COLLATE Latin1_General_CS_AS AS shrt_Txt
      FROM tblLangUS) AS T
GROUP BY shrt_Txt  
HAVING count(*) > 1