How to do a case sensitive search in WHERE clause?

Question

I want case sensitive search in SQL query. But by default, MySQL does not consider the case of the strings.

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

Answer 1

by default, MySQL does not consider the case of the strings

This is not quite true. Whenever you create database in MySQL, the database/schema has a character set and a collation. Each character set has a default collation; see here for more information.

The default collation for character set latin1, which is latin1_swedish_ci, happens to be case-insensitive.

You can choose a case-sensitive collation, for example latin1_general_cs (MySQL grammar):

CREATE SCHEMA IF NOT EXISTS `myschema` 
DEFAULT CHARACTER SET latin1 
COLLATE latin1_general_cs ;

This has an effect on things like grouping and equality. For example,

create table casetable (
  id int primary key, 
  thing varchar(50)
);

select * from casetable;
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
|  4 | ABC   |
|  5 | aBc   |
|  6 | abC   |
+----+-------+

In a case-sensitive database, we get:

select thing, count(*) from casetable group by thing;
+-------+----------+
| thing | count(*) |
+-------+----------+
| ABC   |        1 |
| aBc   |        1 |
| abC   |        1 |
| abc   |        1 |
+-------+----------+

select * from casetable where thing = "abc";
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
+----+-------+

While in a case-insensitive database, we get:

select thing, count(*) from casetable group by thing;
+-------+----------+
| thing | count(*) |
+-------+----------+
| abc   |        4 |
+-------+----------+

select * from casetable where thing = "abc";
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
|  4 | ABC   |
|  5 | aBc   |
|  6 | abC   |
+----+-------+

Note that you can also change the collation from within a query. For example, in the case-sensitive database, I can do

select * from casetable where thing collate latin1_swedish_ci = "abc";
+----+-------+
| id | thing |
+----+-------+
|  3 | abc   |
|  4 | ABC   |
|  5 | aBc   |
|  6 | abC   |
+----+-------+

Answer 2

You always should state with your question which version of MySQL you're using, because MySQL is in steady development.

Okay, back to your question:

The string functions in MySQL are always case sensitive, so you could use any of the functions LOCATE, POSITION, or INSTR.

For example:

SELECT phone FROM user WHERE POSITION('term' IN user_name)>0;

The pattern matching with regular expression (RLIKE or REGEXP) is always case sensitive for all versions of MySQL except the newest 3.23.4.

For example:

SELECT phone FROM user WHERE user_name REGEXP 'term';

For both the normal comparison (=) and the SQL pattern matching (LIKE) the behaviour depends on the fields that are involved:

a. CHAR, VARCHAR, and all variants of TEXT fields do compare case insensitive.

b. CHAR BINARY, VARCHAR BINARY and all variants of BLOB fields do compare case sensitive.

If you compare a field from (a) with a field from (b), then the comparison will be case sensitive (case sensitivity wins). See chapter "7.2.7 String types" of the MySQL Reference Manual and look for the statements on sorting and comparisons.

Starting with V3.23.0 it's also possible to force a comparison into case sensitivity with the cast operator BINARY, independent of the types of involved fields. See chapter "7.3.7 Cast operators" of the MySQL Reference Manual.

So you also might change the type of user_name, or with V3.23.x try something like:

SELECT phone FROM user WHERE BINARY username LIKE '%term%';

Answer 3

This will work in MySQL regardless of the character set.

SELECT 'test' REGEXP BINARY 'TEST' AS RESULT;

Putting 'BINARY' forces a binary comparison.

Answer 4

In my situation, I'm using Access 2010, but I had the same problem yet the solution is different: Use the StrComp() function and test its return to be zero.

StrComp( thing, 'abc', 0) = 0

Because StrComp() returns -1 if the first argument is "smaller", 1 if it is "larger", and 0 if it is "equal", when StrComp()=0, you have a case-sensitive match.

See here, here or here.

Answer 5

Try this for insensitive search, it works fine with great performance:

"SELECT phone FROM user WHERE lower(user_name) like ".srtlower($username);