CODEWITHSUNDEEP
mysqlsqlsql-server-2008oracle11g
mKorbel
mKorbel

Reputation: 109815

Determine CaseSensitive in WHERE clause

for example TableColumn could be contains value in forms New, new or NEW, how can I write query that returns only

SELECT * FROM myTable
WHERE myColumn = 'New'

but doesn't returns TableRows contains new or NEW

Upvotes: 1

Views: 5309

Answers (4)

Pradeep
Pradeep

Reputation: 1

SELECT * FROM users WHERE BINARY userid = 'Rahul';
mysql does not check case of word so in the case of username or userid we shouold need to check case also for more security. I hope this will help you

Upvotes: -1

Sonpal singh Sengar
Sonpal singh Sengar

Reputation: 247

The Best Way to make this column case sensitive is change this particular column if charset is UTF8 change it's collation to collate utf8_bin, after the modification of column it search case sensitive.

i.e I have a table name people with column name "name".

     alter table people 
     modify column name varchar(50) charset utf8 collate utf8_bin;

Note : You can use varchar data type to varbinary, it works fine..

Upvotes: 1

Raj More
Raj More

Reputation: 48018

You can use COLLATE in your where clause

SELECT *
FROM myTable
WHERE myColumn COLLATE latin1_general_cs = 'New'

Upvotes: 3

Galz
Galz

Reputation: 6832

For MySQL, a simple option is:

SELECT * FROM myTable
WHERE myColumn = 'New'
AND BINARY(myColumn) = BINARY('New');

The second condition is logically sufficient, but makes the query slow if the table is big (the Index on myColumn cannot be used). The combination of the 2 conditions allows index use for the first condition, and then filtering out the non matching case.

Upvotes: 7

Related Questions