2

how to search case sensitive data like user_name and password in ms SQL server. In MySQl It is done by BINARY() function.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Ankit Kumar
  • 393
  • 2
  • 3
  • 18

2 Answers2

1

Create column with case sensitive collate, and try this:

Query:

DECLARE @temp TABLE
(
      Name VARCHAR(50) COLLATE Latin1_General_CS_AS
)

INSERT INTO @temp (Name)
VALUES 
    ('Ankit Kumar'),
    ('DevArt'),
    ('Devart')

SELECT * 
FROM @temp
WHERE Name LIKE '%Art'

Output:

DevArt

Or try this similar code -

DECLARE @temp TABLE (Name NVARCHAR(50))

INSERT INTO @temp (Name)
VALUES 
    ('Ankit Kumar'),
    ('DevArt'),
    ('Devart')

SELECT * 
FROM @temp
WHERE Name LIKE '%Art' COLLATE Latin1_General_CS_AS
Devart
  • 119,203
  • 23
  • 166
  • 186
1

Can do this using Casting to Binary

SELECT * FROM UsersTable
WHERE   
    CAST(Username as varbinary(50)) = CAST(@Username as varbinary(50))
    AND CAST(Password as varbinary(50)) = CAST(@Password as varbinary(50))
    AND Username = @Username 
    AND Password = @Password
Pankaj Agarwal
  • 11,191
  • 12
  • 43
  • 59
  • Good answer, but use CAST reduces performance and, in some cases, the server will not be able to use indexes on these columns. – Devart May 27 '13 at 05:31
  • @Devart: Yes, Your answer is actual solution of this question. but i just posted this answer as another approach/solution. – Pankaj Agarwal May 27 '13 at 05:37
  • 2
    I didn't want to criticize your answer :). I just wanted to inform you about possible problems when used this method. – Devart May 27 '13 at 05:41
  • Thank u so much Pankaj and Devart for your quick and accurate solution.It solved my problem. – Ankit Kumar May 27 '13 at 06:34