Find LOWERCASE values

Most of us use default SQL Server collation, which is SQL_Latin1_General_CP1_CI_AS, and is case insensitive.  That means that 'aa' is treated the same as 'AA'.  Unfortunately, even with this collation, we often wind up with situations where we need to actually differentiate between the two values.  Check this out:


USE MyDatabase;


CREATE TABLE dbo.ShortLocates (
  LocateDate DATE,Symbol VARCHAR(25),Shares INT
)
INSERT dbo.ShortLocates (LocateDate,Symbol,Shares)
SELECT '04/27/2010','aa',50000
UNION
SELECT '04/27/2011','AA',50000


SELECT * FROM dbo.ShortLocates
WHERE Symbol = 'aa'


    LocateDate     Symbol   Shares

   2010-04-27         aa     50000
   2011-04-27        AA     50000

Try this to differentiate between lower and upper case:

SELECT LocateDate,Symbol,Shares

FROM dbo.ShortLocates

WHERE CAST(UPPER(Symbol) AS VARBINARY(50)) <> CAST(Symbol AS VARBINARY(50))


   LocateDate     Symbol   Shares

   2010-04-27         aa     50000