SQL Case Sensitive String Compare
SqlSql ServerSql Problem Overview
How do you compare strings so that the comparison is true only if the cases of each of the strings are equal as well. For example:
Select * from a_table where attribute = 'k'
...will return a row with an attribute of 'K'. I do not want this behaviour.
Sql Solutions
Solution 1 - Sql
Select * from a_table where attribute = 'k' COLLATE Latin1_General_CS_AS
Did the trick.
Solution 2 - Sql
You can also convert that attribute as case sensitive using this syntax :
ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(200)
COLLATE SQL_Latin1_General_CP1_CS_AS
Now your search will be case sensitive.
If you want to make that column case insensitive again, then use
ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(200)
COLLATE SQL_Latin1_General_CP1_CI_AS
Solution 3 - Sql
You Can easily Convert columns to VARBINARY(Max Length), The length must be the maximum you expect to avoid defective comparison, It's enough to set length as the column length. Trim column help you to compare the real value except space has a meaning and valued in your table columns, This is a simple sample and as you can see I Trim the columns value and then convert and compare.:
CONVERT(VARBINARY(250),LTRIM(RTRIM(Column1))) = CONVERT(VARBINARY(250),LTRIM(RTRIM(Column2)))
Hope this help.
Solution 4 - Sql
Just as another alternative you could use HASHBYTES, something like this:
SELECT *
FROM a_table
WHERE HASHBYTES('sha1', attribute) = HASHBYTES('sha1', 'k')
Solution 5 - Sql
simplifying the general answer
> SQL Case Sensitive String Compare
These examples may be helpful:
Declare @S1 varchar(20) = 'SQL'
Declare @S2 varchar(20) = 'sql'
if @S1 = @S2 print 'equal!' else print 'NOT equal!' -- equal (default non-case sensitivity for SQL
if cast(@S1 as binary) = cast(Upper(@S2) as binary) print 'equal!' else print 'NOT equal!' -- equal
if cast(@S1 as binary) = cast(@S2 as binary) print 'equal!' else print 'NOT equal!' -- not equal
if @S1 COLLATE Latin1_General_CS_AS = Upper(@S2) COLLATE Latin1_General_CS_AS print 'equal!' else print 'NOT equal!' -- equal
if @S1 COLLATE Latin1_General_CS_AS = @S2 COLLATE Latin1_General_CS_AS print 'equal!' else print 'NOT equal!' -- not equal
The convert is probably more efficient than something like runtime calculation of hashbytes, and I'd expect the collate may be even faster.
Solution 6 - Sql
You can define attribute as BINARY or use INSTR or STRCMP to perform your search.