Posted by Gordon Burditt on 03/18/06 00:03

>>> I tried to use
>>> (1) "SELECT * FROM $table1 as o , $table2 as p WHERE o.name like
>>> '%p.name%'";
>>> but it retrieves nothing at all.

Unless you have a name that contains the string 'p.name', literally,
you won't.

>>> (2) "SELECT * FROM $table1 as o , $table2 as p WHERE o.name like p.name";
>>> will retrieve exact matches only, but this is not what i want.
>>>
>>> (3) SELECT * FROM $table1 as o , $table2 as p WHERE o.name LIKE
>>> CONCAT('%', p.name, '%');
>>> will retrieve exact matches only.
>>>
>>> Any advice will be appreciated.
>
>> Hi,
>
>> I never saw LIKE used in that way ever before, and am unsure if that is
>> possible.

I don't see why not.

>> I only used/saw LIKE with a literal string, not a dynamic construct, like
>> yours, joined on another table.

Granted, it may not be done that way very often, but it's possible.

>> I would advise you to dive into the documentation for your version of mysql,
>> to first check if it is allowed anyway.

I have used:
... WHERE '$email' like whitelist.pattern;

on occasion in email filtering applications. pattern might contain
something like '%@mycustomer.com' or '%@%.mycompany.com'. It's not
real efficient (MySQL probably has to do a patternmatch on every
row, especially if the leading character in the pattern is a %, as
it often is. But it works. And it may be much easier to use
patterns than to use lots more fixed strings and separate tables
for host, user@host, user, etc.

Also possible is:

... WHERE normalize('$email') like normalize(whitelist.pattern);

where the normalize() function converts to lower case, removes leading
and trailing spaces, removes comments, etc. This one is also likely
difficult to optimize. normalize() would either be implemented with
stored procs or be expanded inline with calls to a bunch of other
SQL functions.

Gordon L. Burditt

[Back to original message]