how to search case sensitive data like user_name and password in ms SQL server. In MySQl It is done by BINARY() function.
-
1You keep raw passwords in the database?devnull– devnull2013-05-27 05:22:37 +00:00Commented May 27, 2013 at 5:22
-
Nice guide here: abstraction.net/ViewArticle.aspx?articleID=72diegoperini– diegoperini2013-05-27 05:23:00 +00:00Commented May 27, 2013 at 5:23
-
3same question stackoverflow.com/questions/1831105/…Azadeh Radkianpour– Azadeh Radkianpour2013-05-27 05:25:45 +00:00Commented May 27, 2013 at 5:25
Add a comment
|
2 Answers
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
4 Comments
Devart
Good answer, but use CAST reduces performance and, in some cases, the server will not be able to use indexes on these columns.
Pankaj Agarwal
@Devart: Yes, Your answer is actual solution of this question. but i just posted this answer as another approach/solution.
Devart
I didn't want to criticize your answer :). I just wanted to inform you about possible problems when used this method.
Ankit Kumar
Thank u so much Pankaj and Devart for your quick and accurate solution.It solved my problem.
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