I recently recieved a problem ticket at work because we were showing incorrect data for a particular user. The problem was caused by the fact that the process that creates ids considers ID1234 to be different than id1234. (one of those small requirements issues that gets missed... woops) Anyway I did some searching online and found the COLLATE keyword for SQL Server. The COLLATE keyword allows you to create a SQL statement that overrides the entire databases case sensitivity rules. I have provided an examples below:
Select * from FOO where id ='AbC' COLLATE SQL_Latin1_General_CP1_CS_AS
This would return all rows in the table that had ABC as the ID, but more importantly it would require that A and C be uppercase while b is lowercase. Please note that you could have placed the COLLATE SQL_Latin1_General_CP1_CS_AS text directly after ID instead of including it after the literal.
In addition you could do the following and use the 'IN' keyword.
Select * from FOO where id COLLATE SQL_Latin1_General_CP1_CS_AS IN ('AbC')
The above query again requires that the ID match 'AbC' using case sensative rules. However notice that the COLLATE SQL_Latin1_General_CP1_CS_AS is placed prior to the 'IN' keyword. I have not been able to get SQL Statements that include the 'IN' keyword to work if you put COLLATE SQL_Latin1_General_CP1_CS_AS after the literal.
Some resources I used during my research for this issue are:
No comments:
Post a Comment