Tuesday, May 13, 2014

Character Search With Case Sensitive

Yesterday, one of my co-workers asked how to search for a character pattern within SQL Server with  case sensitive. That mean 'Adam' != 'adam'
I didn't know how. So, here's what I come up with after reading SQL Sever book online.
-- Return the string if string start with 'Adam', but not 'adam' 
DECLARE @str  VARCHAR(50) = 'Adam Smith';

--using COLLATE clause with case sensitive collation
SELECT @str AS CollateResult 
WHERE @str like 'Adam%' COLLATE Latin1_General_CS_AI;

--using binary_checksum function
SELECT @str AS ChecksumResult 
WHERE BINARY_CHECKSUM(SUBSTRING(@str, 1, 4)) = BINARY_CHECKSUM('Adam');

--Using Like keyword. This doesn't work because like can't differentiate
-- between upper and lower case with in a case insensitive SQL Server Instance.
SELECT @str AS LikeResult1 WHERE @str like 'adam%';
SELECT @str AS LikeResult2 WHERE @str like '[a]dam%';















Anyway, I learned something new. Hope you find this post useful. If you know other solution, please let me know. I like to learn different ways to solve the same problem.

References:
binary_checksum function
Collate clause
Like

No comments:

Post a Comment