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?
This is not quite true. Whenever you
The default collation for character set
You can choose a case-sensitive collation, for example
This has an effect on things like grouping and equality. For example,
In a case-sensitive database, we get:
While in a case-insensitive database, we get:
Note that you can also change the collation from within a query. For example, in the case-sensitive database, I can do
Try this for insensitive search, it works fine with great performance:
In my situation, I'm using Access 2010, but I had the same problem yet the solution is different: Use the
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
The pattern matching with regular expression (
For both the normal comparison (=) and the SQL pattern matching (
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
So you also might change the type of user_name, or with V3.23.x try something like: