Home / Questions / 76856
Accepted
bypabloc_
Asked: 2020-06-08 22:56:24 +0800 CST 2020-06-08 22:56:24 +0800 CST

Is there case insensitive search for mysql?

  • 772

For postgresit works easily with ILIKEbut for mysqlI don't find, the only thing I get is cases like this:

SELECT * FROM <table> WHERE LOWER(<key>) LIKE LOWER('%<searchpattern>%')

Thus making all the results of the column and the queried value lowercase. Is there a simpler way or label for mysql.

mysql

2 Answers

  1. Best Answer

    You can indicate the COLLATEto use in the statement. You would only have to indicate a COLLATEcase insensitive. For example:

    SELECT  * FROM  table WHERE key COLLATE UTF8_GENERAL_CI LIKE '%searchpattern%'

    You can also set the COLLATE of the column to be case insensitive so that you don't need to indicate it in the statement. For example:

    ALTER TABLE table MODIFY COLUMN key VARCHAR(…) CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI
    • 3

  2. There is a very simple way:

    SELECT *  FROM `tabla` WHERE BINARY `columna` = 'valor'

    With the keyword Binary you will search for exactly that word.

    • 1