No hits for Uppercase - mysql LIKE

[mnemonic]

Hi,

I'm trying to develop a simple search engine for the articles in a database. I'm using the LIKE command but I never get any hits for searchwords with a first letter uppercase...anyone who knows how to solve this?

The syntax I'm using is like follows:

$sqlquery = "SELECT * FROM $table WHERE $column LIKE '%$searchstring%' ORDER BY $id DESC";

Please...help! 😃

[[deleted]]

Classic problem 🙂

The solution is quite simple; convert the searchstring to uppercase, and compare that with the uppercase version of the content:

SELECT *
FROM table
WHERE UPPER(column) LIKE '%UPPSERCASESEARCHSTRING%';

Note: this will always force MySQL to do a sequencical scan, it cannot use indexes. This is because MySQL does not support functional indexes.
In postgreSQL/MS-SQL/Oracle/Informix you can define a 'functional index' which already has the 'UPPER()' convertion in it. Then SQL can use the index and outperform mysql 1000:1

[mnemonic]

Thanks for trying to help me...

I tried what u suggested, but with no luck. The function UPPER(), does it convert my column in my database temporarily just for the comparison with my SEARCHSTRING? I mean...is the only thing I need to do with my column just write UPPER(column_name) in my sqlquery?

Doesn't matter what type of variables I have in the column to be searched? At present they are TEXT, which I thought was case-insensitive, compared to BLOB, which is case-sensitive.

[mnemonic]

It worked now!!

A little tiny mistake I made ruined it for me...but now it works just fine using your code.

THX vincent!!