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 ne...