SQL like Is Case Sensitive?
When I had this question, I did not find quick results in Portuguese that offered an answer(most of the results are in StackOverflow in English).
It would be interesting to have an objective answer here in Portuguese, to help the community.
- I would like to know if SQL LIKE Is case sensitive?
- is it possible to perform case insensitive search using SQL LIKE ?
- if you search for a name with an accent it is possible that it identify that "Luis" is related to" Luis " and vice versa?
2 answers
I would like to know if SQL LIKE Is case sensitive?
The command LIKE it only makes comparison of string as given pattern passed. Who is responsible for case (in)sensitive is the collation.
Is it possible to perform case insensitive search using SQL LIKE ?
Yes, depending only on the collation configured.
If you search for a name with an accent it is possible that it identifies that "Luis" has relationship with "Luis" and vice versa?
Yes, depending only on the collation configured.
As this answer about What is the difference between charset and collation in database?, understand what is collation :
Snack
Collation is the term used to define the set of rules that the server will use for sorting and comparing texts, or that is, how will the operation of the operators =, > ,
Latin1_general_ci : there is no distinction between capital letters and lowercase. Searching for "Test", records like "test" or " test" will be returned.
Latin1_general_cs : distinguishes capital letters and lowercase. Searching for "test" will only return "Test". Options as "Test" and "test" will not be returned.
Latin1_swedish_ci : No distinguish lowercase and uppercase letters and no accented characters; and with Cedilla, that is, the record containing the word "intuition" will be returned when there is a search for the word "intúicao"
In MySQL the LIKE operator is case-insensitive by default, whether you are looking for Zé, zé, zÉ or ZÉ.
In the case of comparing the accent of Luis and Luis You can simply make use of an OR, for example:
SELECT * FROM <tabela>
WHERE nome LIKE 'Luís' OR nome LIKE 'Luís'
More information can be seen in the mysql documentation.