How to do case sensitive search in the WHERE clause (I use SQL Server)?

Question

I want my case-sensitive case to be executed in my SQL query. But by default, SQL Server does not consider the case of rows.

Any idea on how to do case sensitive search in SQL query?

Answer 1

This can be done by changing the sorting options . By default, 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 .

Answer 2

Using collation or casting in binary format, for example:

 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 

Username / password duplication exists to give the engine the ability to use indexes. The above mapping uses case-sensitive case, change it if necessary.

The second, discarding in binary form, can be performed as follows:

 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 

Answer 3

You can make a request using convert in varbinary - it is very simple. Example:

 Select * from your_table where convert(varbinary, your_column) = convert(varbinary, 'aBcD') 

Answer 4

USE BINARY_CHECKSUM

 SELECT FROM Users WHERE BINARY_CHECKSUM(Username) = BINARY_CHECKSUM(@Username) AND BINARY_CHECKSUM(Password) = BINARY_CHECKSUM(@Password) 

Answer 5

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 the where section

 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 + ' <> ' + ValueB from @example where hashbytes('SHA1', ValueA) <> hashbytes('SHA1', ValueB) -- output: -- a <> A -- a <> b 

or find the meaning

 declare @value_b nvarchar(1) = 'A' select ValueB + ' = ' + @value_b from @example where hashbytes('SHA1', ValueB) = hasbytes('SHA1', @value_b) -- output: -- A = A 

Answer 6

use Latin1_General_CS as your sort in your sql db

Answer 7

In MySQL, if you do not want to change the sorting and want to perform case-sensitive searches, simply use the binary keyword:

 SELECT * FROM table_name WHERE binary username=@search_parameter and binary password=@search_parameter 

Answer 8

Note: this was for MySQL / Postgres / Microsoft SQL Server, but it also worked on DB2.

I suggested that there is an easier way to do this without any extra work. for example, you can use the UPPER function (or lower). This helped me find any variation of Athabasca:

WHERE UPPER (ADDR_CITY) AS TOP (Athabasca)

Work with your mind, not force! :) Keenan

Answer 9

Like others, you can perform case-sensitive searches. Or just change the sort format of the specified column like me. For the User / Password columns in my database, I change them to sort using the following command:

 ALTER TABLE 'UserAuthentication' CHANGE 'Password' 'Password' VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL;