SQL Case Sensitive String Compare

Question

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

User · Accepted Answer

Select   from a table where attribute    k  COLLATE Latin1 General CS AS    Did the trick

User · Answer

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

User · Answer

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

User · Answer

You can define attribute as BINARY or use INSTR or STRCMP to perform your search

User · Answer

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

User · Answer

Just as another alternative you could use HASHBYTES  something like this   SELECT    FROM a table  WHERE HASHBYTES  sha1   attribute    HASHBYTES  sha1    k

[sql] SQL Case Sensitive String Compare

Examples related to sql

Examples related to sql-server