-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: 3. februar 2004 00:14
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] How smart is sql...comparing strings

At 08:26 PM 2/02/2004 +0100, you wrote:

>>Hi, I have a stored procedure and in one of my WHERE clauses I ask

this:

>>
>>select ...
>>from ...
>>where col1 > '123'
>>
>>Here '123' is a string (VarChar), so my question is whether sql is

able

>>to compare strings.

>Sure - but it compares them as strings (alphanumeric sequence), not as
>numbers. So if col1 is '1' it will be higher than '123'. 'Tis not a
>question of "How smart is sql?"...but of "How smart is the question?"

>/hb

Hi, it maybe was a dumb question but sometimes when I'm tired I ask
stupid questions.

> So if col1 is '1' it will be higher than '123'.

How can '1' > '123'? I did a little test like this in a stored
procedure:

if ('1' > '123') then
begin
OUT_FLAG = -10;
exit;
end
else
begin
OUT_FLAG = -20;
exit;
end

And this returns -20. What am I missing? (Probably a lot...)

Another thing, I have written a SP that converts from string to numbers,
but as far as I know it is not possible to do something like this:

sel ...
from ...
where SP_STRINGTONUM(col1) > 123

But the same thing is possible with UDFs? Like:

sel ...
from ...
where UDF_STRINGTONUM(col1) > 123

If so are there any UDFs freely available? I couldn't find any in
fbudf.dll or ib_udf.dll.

Henrik