Just puzzled ...

select * from products where prodname like 'SUGARB' escape 'B' generates an
'invalid escape sequence' error.

Maybe the - escape 'B'- part not honored by IB_Wisql ?

-----------------------------------------

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <ib-support@yahoogroups.com>
Sent: Tuesday, April 08, 2003 8:22 PM
Subject: Re: [ib-support] LIKE problem


> 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
>
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>