CODEWITHSUNDEEP
mysqlcollationcase-sensitive
Wern Ancheta
Wern Ancheta

Reputation: 23297

how to search for exact string in mysql

I'm trying to search for an exact match of a string in mysql. The string is 'nrew'. But when I do the queries below, I still get a result:

SELECT UserID FROM sys_users WHERE UserID='NREW'
SELECT UserID FROM sys_users WHERE UserID='NrEw'

Please help.

Upvotes: 25

Views: 31752

Answers (5)

Danyun Liu
Danyun Liu

Reputation: 3092

You can use keyword Binary,

SELECT UserID FROM sys_users WHERE BINARY UserID='nrew'

refer to here

Upvotes: 9

sje397
sje397

Reputation: 41812

One method is to use LIKE BINARY instead of =:

SELECT UserID FROM sys_users WHERE UserID LIKE BINARY 'nrew';

Upvotes: 4

Roland Mai
Roland Mai

Reputation: 31077

SELECT UserID FROM sys_users WHERE BINARY UserID='NREW'

Upvotes: 41

Michael Mior
Michael Mior

Reputation: 28752

The default collation which MySQL uses to make comparisons is case insensitive. You need to specify a case sensitive collation or binary. You can either do this when creating the column, or in the query.

For example:

SELECT UserID FROM sys_users WHERE UserID='NREW' COLLATE latin1_bin

The proper collation depends on your character set. For latin1, the default, you can use latin1_bin. For utf8, utf8_bin.

Upvotes: 9

ElonU Webdev
ElonU Webdev

Reputation: 2459

Give this is try: http://www.devx.com/tips/Tip/13043

Select UserID from sys_users where convert(varbinary, UserID) = convert(varbinary, 'NREW')

Or try COLLATE

http://aspadvice.com/blogs/ssmith/archive/2007/09/30/Case-Sensitive-or-Insensitive-SQL-Query.aspx

Upvotes: 1

Related Questions