How can I search case-insensitive in a column using LIKE wildcard

Question

I looked around some and didn t find what I was after so here goes   SELECT   FROM trees WHERE trees  title  LIKE    elm     This works fine  but not if the tree is named Elm or ELM etc     How do I make SQL case insensitive for this wild-card search   I m using MySQL 5 and Apache

User · Answer

I ve always solved this using lower   SELECT   FROM trees WHERE LOWER  trees title   LIKE    elm

User · Answer

You can use the following method      private function generateStringCondition  value       field        operator        regex        wildcardStart        wildcardEnd                if  value                      where      field  regex   wildcardStart  strtolower  value    wildcardEnd                   searchArray   explode       value                if sizeof  searchArray   gt  1                    foreach   searchArray as  key  gt  value                        where     operator  field  regex   wildcardStart  strtolower  value    wildcardEnd                                               else               where                         return  where          use this method like below      where     this- gt generateStringCondition  yourSearchString    LOWER columnName    or    like                  sql    select   from table  where

User · Answer

SELECT    FROM    trees WHERE   trees  title  COLLATE UTF8 GENERAL CI LIKE   elm     Actually  if you add COLLATE UTF8 GENERAL CI to your column s definition  you can just omit all these tricks  it will work automatically    ALTER TABLE trees   MODIFY COLUMN title VARCHAR      CHARACTER   SET UTF8 COLLATE UTF8 GENERAL CI     This will also rebuild any indexes on this column so that they could be used for the queries without leading

User · Answer

well in mysql 5 5   like operator is insensitive   so if your vale is elm or ELM or Elm or eLM or any other   and you use like   elm      it will list all the matching values   I cant say about earlier versions of mysql   If you go in Oracle   like work as case-sensitive   so if you type like   elm     it will go only for this and ignore uppercases    Strange   but this is how it is

User · Answer

use ILIKE  SELECT   FROM trees WHERE trees  title  ILIKE   elm      it worked for me

User · Answer

The case sensitivity is defined in the columns   tables   database collation settings  You can do the query under a specific collation in the following way   SELECT   FROM trees WHERE trees  title  LIKE   elm   COLLATE utf8 general ci   for instance    Replace utf8 general ci with whatever collation you find useful   The  ci stands for case insensitive

User · Answer

This is the example of a simple LIKE query   SELECT   FROM  lt table gt  WHERE  lt key gt  LIKE    lt searchpattern gt      Now  case-insensitive using LOWER   func   SELECT   FROM  lt table gt  WHERE LOWER  lt key gt   LIKE LOWER    lt searchpattern gt

User · Answer

Simply use     SELECT   FROM  trees  WHERE LOWER trees  title   LIKE    elm       Or Use   SELECT   FROM  trees  WHERE LCASE trees  title   LIKE    elm       Both functions works same

User · Answer

When I want to develop insensitive case searchs  I always convert every string to lower case before do comparasion

User · Answer

You must set up proper encoding and collation for your tables   Table encoding must reflect the actual data encoding  What is your data encoding    To see table encoding  you can run a query SHOW CREATE TABLE tablename

User · Answer

You don t need to ALTER any table  Just use the following queries  prior to the actual SELECT query that you want to use the wildcard       set names  utf8       SET COLLATION CONNECTION utf8 general ci      SET CHARACTER SET CLIENT utf8      SET CHARACTER SET RESULTS utf8

User · Answer

Non-binary string comparisons  including LIKE  are case insensitive by default in MySql  https   dev mysql com doc refman en case-sensitivity html

User · Answer

SELECT name         FROM gallery         WHERE CONVERT name USING utf8  LIKE  utf8    q           GROUP BY name COLLATE utf8 general ci LIMIT 5

User · Answer

I m doing something like that   Getting the values in lowercase and MySQL does the rest       string     GET  string        mysqli query  con  SELECT                          FROM table name                        WHERE LOWER column name                         LIKE LOWER    string         And For MySQL PDO Alternative            string     GET  string             q    SELECT                 FROM table name               WHERE LOWER column name                LIKE LOWER                query    dbConnection- gt prepare  q            query- gt bindValue 1     string    PDO  PARAM STR            query- gt execute

User · Answer

I think this query will do a case insensitive search   SELECT   FROM trees WHERE trees  title  ILIKE   elm

[mysql] How can I search (case-insensitive) in a column using LIKE wildcard?

Examples related to mysql

Examples related to sql

Examples related to sql-like

Examples related to case-insensitive