Tutorial :How to use case sensitivity in instr() in MySQL?



Question:

Possible duplicate: How to apply wildcard in instr() in MySQL?

The possible duplicate linked shows a query which is exactly like my current one sort of. However, I cannot find a way to make it a case sensitive match. :\

SELECT COUNT(*) FROM users WHERE INSTR(flags, 'T') > 0;  

I get a count of 46 which is obviously wrong. It's counting every instance "T" in flags whether it's upper- or lower-case.

It works as according to the MySQL documentation. I found something in the MySQL docs that said to put an "@" symbol on a variable to make it a case sensitive match. However, I tried this on the @'T' to form the following query:

SELECT COUNT(*) FROM users WHERE INSTR(flags, @'T') > 0;  

I get a count of zero.

Could anyone shed some light on this for me? :)

EDIT:

Forgot to mention, sorry. I also tried a LIKE '%T%' as the where-clause, which still failed and returns the same as (INSTR(flags, 'T') > 0);


Solution:1

Tested,

SELECT binary 'AKkRBbagSsPpCtmxeGhUX' LIKE '%T%';  

produces '0', as expected. According to the String Comparison documentation, LIKE performs a binary (thus, case-sensitive) comparison if either of its operands are binary.


Solution:2

I believe this will work

SELECT COUNT(*) FROM users WHERE INSTR(binary flags, 'T') > 0;  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »