Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Like Case Sensitive in MySQL

I have a MySQL query:

SELECT concat_ws(title,description) as concatenated HAVING concatenated LIKE '%SearchTerm%'; 

And my table is encoded utf8_general_ci with MyISAM.

Searches seem to be case sensitive.

I can't figure out how to fix it. What's going wrong and/or how do I fix it?

like image 576
Pmc Machinery Avatar asked Nov 10 '11 17:11

Pmc Machinery


People also ask

Is like in SQL case-sensitive?

LIKE performs case-insensitive substring matches if the collation for the expression and pattern is case-insensitive.

How do I create a like case-insensitive query in MySQL?

select * from users where lower(first_name) = 'ajay'; The method is to make the field you are searching as uppercase or lowercase then also make the search string uppercase or lowercase as per the SQL function.

Is SQL not like case-sensitive?

Let's start there. Keywords in SQL are case-insensitive for the most popular DBMSs. The computer doesn't care whether you write SELECT , select, or sELeCt ; so, in theory, you can write however you like.

How do I make SQL not like case-sensitive?

Using LOWER( ad UPPER() functions for case sensitive queries In the similar fashion UPPER() and LOWER() functions can be used in the LIKE clause for getting similar records and making the search insensitive in the table or database having collation that shows CS that is case sensitive in its collation.


Video Answer


1 Answers

A much better solution in terms of performance:

SELECT .... FROM .... WHERE `concatenated` LIKE BINARY '%SearchTerm%'; 

String comparision is case-sensitive when any of the operands is a binary string.

Another alternative is to use COLLATE,

SELECT .... FROM .... WHERE `concatenated` like '%SearchTerm%' COLLATE utf8_bin; 
like image 167
kolypto Avatar answered Sep 23 '22 09:09

kolypto