How to do a case sensitive search in WHERE clause I m using SQL Server

Question

I want to do a case sensitive search in my SQL query  But by default  SQL Server does not consider the case of the strings   Any idea on how to do a case sensitive search in SQL query

User · Answer

use  Latin1 General CS as your collation in your sql db

User · Answer

Just as others said  you can perform a case sensitive search  Or just change the collation format of a specified column as me  For the User Password columns in my database I change them to collation through the following command   ALTER TABLE  UserAuthentication  CHANGE  Password   Password  VARCHAR 255  CHARACTER SET latin1 COLLATE latin1 general cs NOT NULL

User · Answer

Can be done via changing the Collation  By default it is case insensitive   Excerpt from the link   SELECT 1 FROM dbo Customers WHERE   CustID    CustID COLLATE SQL Latin1 General CP1 CS AS     AND CustPassword    CustPassword COLLATE SQL Latin1 General CP1 CS AS   Or  change the columns to be case sensitive

User · Answer

USE BINARY CHECKSUM  SELECT  FROM Users WHERE        BINARY CHECKSUM Username    BINARY CHECKSUM  Username      AND BINARY CHECKSUM Password    BINARY CHECKSUM  Password

User · Answer

By using collation or casting to binary  like this   SELECT   FROM Users WHERE        Username    Username COLLATE SQL Latin1 General CP1 CS AS     AND Password    Password COLLATE SQL Latin1 General CP1 CS AS     AND Username    Username      AND Password    Password    The duplication of username password exists to give the engine the possibility of using indexes  The collation above is a Case Sensitive collation  change to the one you need if necessary    The second  casting to binary  could be done like this   SELECT   FROM Users WHERE        CAST Username as varbinary 100     CAST  Username as varbinary       AND CAST Password as varbinary 100     CAST  Password as varbinary 100       AND Username    Username      AND Password    Password

User · Answer

You can make the query using convert to varbinary     it   s very easy  Example   Select   from your table where convert varbinary  your column    convert varbinary   aBcD

User · Answer

use HASHBYTES  declare  first value nvarchar 1     a  declare  second value navarchar 1     A   if HASHBYTES  SHA1   first value    HASHBYTES  SHA1   second value  begin     print  equal  end else begin     print  not equal  end  -- output  -- not equal      in where clause  declare  example table  ValueA nvarchar 1   ValueB nvarchar 1    insert into  example  ValueA  ValueB  values    a    A              a    a              a    b    select  ValueA           ValueB from     example where   hashbytes  SHA1   ValueA    hashbytes  SHA1   ValueB   -- output  -- a   a  select  ValueA      lt  gt      ValueB from     example where   hashbytes  SHA1   ValueA   lt  gt  hashbytes  SHA1   ValueB   -- output  -- a  lt  gt  A -- a  lt  gt  b   or to find a value  declare  value b nvarchar 1     A   select  ValueB            value b from     example where   hashbytes  SHA1   ValueB    hasbytes  SHA1    value b   -- output  -- A   A

User · Answer

In MySQL if You don t want to change the collation and want to perform case sensitive search then just use binary keyword like this   SELECT   FROM table name WHERE binary username  search parameter and binary password  search parameter

User · Answer

select   from incidentsnew1  where BINARY CHECKSUM CloseBy    BINARY CHECKSUM Upper CloseBy

[sql] How to do a case sensitive search in WHERE clause (I'm using SQL Server)?

Examples related to sql

Examples related to sql-server