At 01:47 AM 9/04/2003 +0000, you wrote:

>Hi all,
>
>The LIKE predicate doesn't seem to work if you use a constant value
>as the comparison value, and a column value as the value to test -
> "'[value]' LIKE [column containing expression]".
>
>For example, if I do:
>CREATE TABLE foo (addr CHAR(64));
>INSERT INTO foo (addr) VALUES('test@...');
>SELECT * FROM foo WHERE addr LIKE 'test%';
>the select returns the row correctly.
>
>but if I do:
>CREATE TABLE foo (addr CHAR(64));
>INSERT INTO foo (addr) VALUES('test%');
>SELECT * FROM foo WHERE addr LIKE 'test@...';
>the select returns no results (the query doesn't fail, it just
>doesn't match any rows).
>
>I'm pretty sure it's a bug, and I tried it on mysql and it works fine
>there. Any suggestions, before I file a bug report?

LOL, it's not a bug!
LIKE won't work without the '%' symbol in the search string. IOW, the idea
is that you provide the wildcard character to the search string; but it
doesn't work the other way round, where you provide a constant and it will
hunt for a literal which contains a character which *happens* to be the
same as a wildcard.

If it "works" in mysql then you are looking at a non-standard SQL
implementation there, not at some "need-to-have" for a standards-compliant
RDBMS that takes itself seriously.

It's an exception of sorts if you don't provide a wildcard, but it's just
getting swallowed and the query correctly returns no matches. The correct
wildcard query for a literal stored as 'test%' would be LIKE 'test%'. If
you wanted to get only values starting with 'test%' (i.e. '%' stored as a
literal) you can mark the literal '%' character using the 'ESCAPE' sequence
in your search string. So, to find the value you inserted into the second
example, you would do
WHERE addr LIKE 'test@%%' ESCAPE '@'

Alternatively, you could use STARTING WITH 'test%'.

heLen