Like search is case sensitive?

Question

I have a query like this, executed on a table where there are a couple keywords comma seperated in the field keywords with a leading and trailing comma:

SELECT media_id, filename FROM media WHERE keywords LIKE '%,house,%'

However, it won't find anything (there are a couple thousand records in that table, quite a bunch of which have the keyword "House"). Even searching for '%,House,%' does find nothing.

What works is doing the like search in all caps:

SELECT media_id, filename FROM media WHERE keywords LIKE '%,HOUSE,%'

Now it will also find keywords like House.

Collation of the table and the keywords column is latin1_german1_ci.

Where does this behaviour come from?
How do I get the usual completely case-insensitive behaviour?

@Vatev Collation of table and column is `latin1_german1_ci`. — Cobra_Fast, Jun 28 '13 at 13:52
for comma separated value you can use [FIND_IN_SET](http://dev.mysql.com/doc/refman/5.0/en/string-functions.html) — Maxim Krizhanovsky, Jun 30 '13 at 16:00

score 2 · Answer 1 · answered Jun 28 '13 at 14:53

2

When case sensitivity comes into play I will often LOWER(keywords) like ('%house%')

answered Jun 28 '13 at 14:53

RazorSky

360
2
8

score 0 · Answer 2 · answered Jun 28 '13 at 13:59

0

You can change the collation of the column OR you can cast it like this:

SELECT media_id, filename FROM media WHERE keywords LIKE '%,house,%' COLLATE utf8_general_ci

answered Jun 28 '13 at 13:59

Sander Visser

4,144
1
31
42

1

To my understanding `_ci` collations are *c*ase *i*nsensitive and `latin1_german1_ci` is one of them. Are they not? – Cobra_Fast Jun 28 '13 at 14:06

score 0 · Accepted Answer · answered Jul 15 '13 at 10:23

0

Extremely stupid and unsolvable problem.

I expanded keywords like or or and to actual query logic even if they were within a word in the preprocessing script, so searching for handsome would be expanded to h && some.

I'm sorry.

answered Jul 15 '13 at 10:23

Cobra_Fast

15,671
8
57
102

Like search is case sensitive?

3 Answers3