68

I am trying to execute the sql query:

select * from table where column like '%value%';

But the data is saved as 'Value' ( V is capital ).

When I execute this query i don't get any rows. How do i make the call such that, it looks for 'value' irrespective of the casing of the characters ?

Muntasir
  • 798
  • 1
  • 14
  • 24
user2583714
  • 1,097
  • 2
  • 11
  • 18

9 Answers9

131

use LOWER Function in both (column and search word(s)). Doing it so, you assure that the even if in the query is something like %VaLuE%, it wont matter

select qt.*
from query_table qt
where LOWER(column_name) LIKE LOWER('%vAlUe%');
JGutierrezC
  • 4,398
  • 5
  • 25
  • 42
21

If you want this column be case insensitive :

ALTER TABLE `schema`.`table` 
CHANGE COLUMN `column` `column` TEXT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

Thus, you don't have to change your query.

And the MySQL engine will process your query quicker than using lower() function or any other tricks.

And I'm not sure that using lower function will be a good solution for index searching performance.

kmas
  • 6,401
  • 13
  • 40
  • 62
15

Either use a case-insensitive collation on your table, or force the values to be lower case, e.g.

WHERE lower(column) LIKE lower('%value%');
Marc B
  • 356,200
  • 43
  • 426
  • 500
8

Try using a case insensitive collation

select * from table
where column like '%value%' collate utf8_general_ci
juergen d
  • 201,996
  • 37
  • 293
  • 362
7

Use the lower() function:

select t.*
from table t
where lower(column) like '%value%';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
6

If you are using PostgreSQL, a simpler solution is to use insensitive like (ILIKE):

SELECT * FROM table WHERE column ILIKE '%value%'
Alter Lagos
  • 12,090
  • 1
  • 70
  • 92
5

you should use either lower or upper function to ignore the case while you are searching for some field using like.

select * from student where upper(sname) like 'S%';

OR 

select * from student where lower(sname) like 'S%';
Vikrant
  • 4,920
  • 17
  • 48
  • 72
Govindu Surla
  • 51
  • 1
  • 1
3

I know this is a very old question, but I'm posting this for posterity:
Non-binary string comparisons (including LIKE) are case-insensitive by default in MySql: https://dev.mysql.com/doc/refman/en/case-sensitivity.html

Marko Bonaci
  • 5,622
  • 2
  • 34
  • 55
0

This will eventually do the same thing. The ILIKE works, irrespective of the casing nature

SELECT * FROM table WHERE column_name ILIKE "%value%"

kelvin93
  • 11
  • 2
  • There is no ILIKE in mysql, my guess that is a Postgre query. – mxix Nov 11 '20 at 12:04
  • But this is the same as the answer [already done here](https://stackoverflow.com/questions/18853452/sql-select-like-insensitive-casing/44121586#44121586) four years ago. I dont' see something valuable that hasn't been told in the other answer. – Alter Lagos Jun 15 '21 at 06:12