String Comparison in MS SQL
-
As you know, the =, like operators for string comparison in MS SQL are just for not-case sensitive string comparison. So my concern is how to compare two strings in case sensitive mode? Thank in advance!
It is to do with the collation sequence. If the collation sequence is set to a Case-sensitive one then you will get case sensitive comparisons. SQL Server by default uses, IIRC, Latin1_General_AS_CI which is Accent Sensitive & Case Insensitive
Do you want to know more? WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
-
As you know, the =, like operators for string comparison in MS SQL are just for not-case sensitive string comparison. So my concern is how to compare two strings in case sensitive mode? Thank in advance!
Cast the string to varbinarys and then compare - here is a small script you can run in Query Analyzer to test it if you would like.
DECLARE @Str1 VARCHAR(20) DECLARE @Str2 VARCHAR(20) DECLARE @Str3 VARCHAR(20) SET @Str1 = 'abcdefg' SET @str2 = 'ABCDEFG' SET @Str3 = 'abcdefg' IF @Str1 = @str2 Print 'Case Insentative: Equal' ELSE Print 'Case Insentative: Not Equal' IF CAST(@Str1 AS VARBINARY(50)) = CAST(@Str2 AS VARBINARY(50)) Print 'Case Sensitive 1: Equal' ELSE Print 'Case Sensitive 1: Not Equal' IF CAST(@Str1 AS VARBINARY(50)) = CAST(@Str3 AS VARBINARY(50)) Print 'Case Sensitive 2: Equal' ELSE Print 'Case Sensitive 2: Not Equal' -
Cast the string to varbinarys and then compare - here is a small script you can run in Query Analyzer to test it if you would like.
DECLARE @Str1 VARCHAR(20) DECLARE @Str2 VARCHAR(20) DECLARE @Str3 VARCHAR(20) SET @Str1 = 'abcdefg' SET @str2 = 'ABCDEFG' SET @Str3 = 'abcdefg' IF @Str1 = @str2 Print 'Case Insentative: Equal' ELSE Print 'Case Insentative: Not Equal' IF CAST(@Str1 AS VARBINARY(50)) = CAST(@Str2 AS VARBINARY(50)) Print 'Case Sensitive 1: Equal' ELSE Print 'Case Sensitive 1: Not Equal' IF CAST(@Str1 AS VARBINARY(50)) = CAST(@Str3 AS VARBINARY(50)) Print 'Case Sensitive 2: Equal' ELSE Print 'Case Sensitive 2: Not Equal'