MySQL upper/lowercase search problem

[Guiri]

After upgrading to MySQL 4.1 from 4.0 I have a customer that when he makes a search it returns different results depending if the word starts with Upper or Lower case. (He would like a search for John or john to return the same results).

Does anyone have an idea on how to fix this?

Example:

SELECT id FROM latin_news where id>'0' AND sp_text LIKE '%john%' (returns 0 results)

SELECT id FROM latin_news where id>'0' AND sp_text LIKE '%John%' (returns 3 results)

[superwormy]

I think MySQL should only do that if the field is declared BINARY or something... you might want to check the table structure.

In any case, you could also do this:

SELECT id FROM latin_news where id>'0' AND LOWER(sp_text) LIKE LOWER('%john%')

[Guiri]

I tried 'SELECT id FROM latin_news where id>'0' AND LOWER(sp_text) LIKE LOWER('%john%')' but it returns 0 results just the same. Also if I try 'SELECT id FROM latin_news where id>'0' AND LOWER(sp_text) LIKE LOWER('%John%')' it now returns 0 results.