SQL LIKE is Case Sensitive(Case Sensitive)?

Asked

Viewed 4,615 times

5

By having this doubt, I did not find quick results in Portuguese that offer 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 wonder if SQL LIKE is case sensitive?
  • It is possible to perform case insensitive search (if sensitive) using SQL LIKE ?
  • If you search for a name with accentuation it is possible that it identifies that "Luis" has relationship with "Luis" and vice versa?
mysql sql case-insensitive cash-sensitivity

  • Depends on the DBMS. Which one you are using?

  • If it is Sqlserver for example this comparison depends on the Collation of the field, he who defined this type of comparison

  • It is not the operator that differentiates between upper and lower case, it is the column itself as our friend Jeferson Almeida mentioned above.

  • I updated the question and includes the Mysql tag, which is what I use.

  • 1

    If the Collation is *_ci he is case insensitive, ie both Luís how much luís is the same. Now, accentuation is another story, which also depends on the Collation, if you are using the utf8_unicode_ci will ignore the accents too. Possible related http://answall.com/questions/30329/qual-a-diff%C3%A7a-entre-charset-e-collation-em-database.

2 answers

5


I wonder if SQL LIKE is case sensitive?

The command LIKE just makes comparison of string as determined pattern passed. Who is responsible for the case (in)sensitive is the collation.

It is possible to perform case insensitive search (if sensitive) using SQL LIKE ?

Yes, depending only on the collation configured.

If you search for a name with accentuation it is possible that it identifies that "Luis" has relationship with "Luis" and vice versa?

Yes, depending only on the collation configured.

In accordance with this answer about What is the difference between charset and collation in a database?, understand what is collation:

Collation

Collation is the term used to define the set of rules that the server will use for sorting and comparing text, or be, how the operators =, >, <, order by, etc. For example, depending on the configured Collation, the system will sort the'ö' character between the ːo' and ːp' characters, using another collation, this character can be ordered in another position. Therefore can give conflict when making queries that relate tables to different collations. In addition, the collation also defines whether the system will differentiate accented characters or if it will be case sensitive, for example the collation Latin1_general_ci_as defines the system should treat characters as case insensitive (CI) and acentue sensitive (AS). Examples:

latin1_general_ci: No distinction between capital letters and lowercase. Searching for "test", records such as "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: Not distinguishes between lower and upper case letters and no accented characters, and with cedilla, that is, the record that contains the word "intuition" will be returned when there is a search for the word "intúicao"

-2

In Mysql the LIKE operator is case-insensitive by default, whether you are looking for Joe, Joe, Zoe 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 on mysql documentation, in English.

Browser other questions tagged mysql sql case-insensitive cash-sensitivity

You are not signed in. Login or sign up in order to post.