Sql wildcars, capital letters causing problems

Question

Have a really simpel wildcars search:

"SELECT firstname, lastname, email, text FROM members WHERE firstname LIKE '%$word%' OR lastname LIKE '%$word%' OR email LIKE '%$word%' OR text LIKE '%$word%'"

If I search for "test" it finds the words "test" but not "Test", how do i fix this? The weird thing is that in other another db it finds the words "Test" and "test", they are bout running "latin1_swedish_ci".

I thought the "COLLATE" statement would help, but i could not get it to work :(

Hello. This should help you? http://stackoverflow.com/questions/8083455/mysql-like-case-sensitive — Velja Radenkovic, Feb 16 '13 at 11:57

score 0 · Accepted Answer · answered Feb 16 '13 at 12:20

"SELECT firstname, lastname, email, text FROM members WHERE UPPER(firstname) LIKE UPPER('%$word%') OR UPPER(lastname) LIKE UPPER('%$word%') OR UPPER(email) LIKE UPPER('%$word%') OR UPPER(text) LIKE UPPER('%$word%')"

try do it like this it will search after converting every text to upper case

score 0 · Answer 2 · answered Feb 17 '13 at 17:59

"SELECT firstname, lastname, email, text 
FROM members 
WHERE upper(firstname) LIKE upper('%$word%') 
OR upper(lastname) LIKE upper('%$word%') 
OR upper(email) LIKE upper('%$word%') 
OR upper(text) LIKE upper('%$word%')"

"SELECT firstname, lastname, email, text 
FROM members 
WHERE lower(firstname) LIKE lower('%$word%') 
OR lower(lastname) LIKE lower('%$word%') 
OR lower(email) LIKE lower('%$word%') 
OR lower(text) LIKE lower('%$word%')"

this should work :)

Sql wildcars, capital letters causing problems

2 Answers2