--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@...> wrote:

>
> Roger Crämer wrote:
>
> [ a table test with a single field x and a single row containing 4711]
>
> >
> > The select
> > select * from test where x = 'TEST'
> > results in the correct error
> > select * from test where x = 'TEST';
> >
> > X
> > ============
> > Statement failed, SQLCODE = -413
> >
> > conversion error from string "Kühe"
>
> Unh. That one stopped me cold - how did we go from an English
> test to German cows?

OOoops...
Simple CCP (Cut Copy Paste) error - I mixed two tests. The original
test occured with a where statement containing german umlauts and we
first thought that was the problem.

> >
> > But the following select do not return anything:
> > select * from test where x like 'TEST%';
> > select * from test where x like 'TEST';
> >
> > Should the server not respond with the same error as in the '='

select?

>
> No. The rules are different, according to the standard. I should
> probably look up the details, but LIKE isn't like =
>
> Regards,
>
>
> Ann
>
> Logic is a way of going wrong with confidence.
>

I looked up the SQL92 standard and found the following:

...
<like predicate> ::=
<match value> [ NOT ] LIKE <pattern>
[ ESCAPE <escape character> ]

<match value> ::= <character value expression>

<pattern> ::= <character value expression>

<escape character> ::= <character value expression>

...

Syntax Rules

1) The data types of <match value>, <pattern>, and <escape char-
acter> shall be character string. <match value>,
<pattern>, and
<escape character> shall be comparable.

....

So in my opinion the comparison in the specified sql statements should
fail - or the numeric value is converted into a string before; lets do
a test:
select * from test where x like '%7%'

X
============
4711

It seems that the numeric value is (internally) converted to a string
and the like operator works on that string. But I don't know if that
is standard conform.


Roger