SQL-problem with "LIKE"

[Anon]

Hi there,

I'm encountering a lil' problem when I try to do a SQL query. I wrote this:

   "SELECT name FROM products WHERE description LIKE '%my_text%'";

Although I am very sure that my table 'products' contains a field with 'my_text', I get an empty result. Why!?!

By the way, description is a TEXT field (no specific length), could that be the cause? TIA.

Greetz,

Vincent Driessen
Venlo, The Netherlands

[Anon]

Not sure which SQL product you are using, so the answer varies. With MySQL, just use RLIKE or REGEXP instead of LIKE and you can use all the regular expressions you want.

[Anon]

What does RLIKE do?

[Anon]

i think with SQL, you cant seach any text or blob fields...

[Anon]

I think it really depends upon the database you are using and how they implemented SQL. The O'Reilly MySQL and mSQL book I have says mSQL can't use LIKE on TEXT entries. MySQL works fine for me with LIKE and RLIKE (aka REGEXP). The % stands for any number of characters and the _ matches just one. If you need to use complicated regular expressions, use RLIKE instead. For example:

SELECT name FROM products WHERE description RLIKE '^{ABC.*[0-9]{5}'}

which would only select those with a description beginning with ABC, any number of characters, then ending in exactly five numbers. The % works pretty well for plain wildcards, but can't be that specific.