Menu
case-insensitive search of MySQL?
For my site search, what is the most efficient way of to query my db for a word/phrase regardless of case?
885
select * from users where lower(first_name) = 'ajay'; The method is to make the field you are searching as uppercase or lowercase then also make the search string uppercase or lowercase as per the SQL function.
Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case-sensitive.
By default, it depends on the operating system and its case sensitivity. This means MySQL is case-insensitive in Windows and macOS, while it is case-sensitive in most Linux systems. However, you can change the behavior by changing collation.
SQL Case insensitivity is to use the query statements and the keywords tables and columns by specifying them in capital or small letters of alphabets. SQL keywords are by default set to case insensitive that means that the keywords are allowed to be used in lower or upper case.
If your database / table is not set up with a case-insensitive collate you need to append something like COLLATE utf8_general_ci to your query. (The _ci suffix stands for case insensitive.)
Have a look at the documentation: 9.1.7.1. Using COLLATE in SQL Statements:
With the COLLATE clause, you can override whatever the default collation is for a comparison. COLLATE may be used in various parts of SQL statements.
64
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
