php/mysql: how to search case insentitively

Question

i have this table in UTF8 (collation is utf8_bin in case it matters).

I need to search in it for strings using LIKE '%$searchstring%' .

Right now you have to use the exact case in order for the results to show up. Is there a way to make it case insensitive ?

Thank you

UPDATE sorry, found the answer.

OMG Ponies · Accepted Answer · 2009-10-30 19:30:13Z

2

Use:

WHERE LOWER(t.column) LIKE LOWER('%search_string%')

..or:

WHERE UPPER(t.column) LIKE UPPER('%search_string%')

References:

LOWER
UPPER

answered Oct 30, 2009 at 19:30

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

akavel Over a year ago

according to stackoverflow.com/questions/234591/upper-vs-lower-case/…, comparison with 'lower' or 'upper' can be incorrect for some languages

Halil Özgür Over a year ago

What about ... (etc COLLATE utf8_<lang>_ci) LIKE '%searchstring%'? By the way, if you want "Çilek" to show up in results when the user searches for "cilek", you can use utf8_unicode_ci, that is, both case insensitive and collation insensitive search.

AJ. · Accepted Answer · 2009-10-30 19:29:05Z

0

Your collation specifies the case-sensitivity. Use this instead:

utf8_ci

answered Oct 30, 2009 at 19:29

AJ.

28.3k19 gold badges89 silver badges98 bronze badges

Collectives™ on Stack Overflow

php/mysql: how to search case insentitively

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related