At 09:49 AM 3/02/2004 +0100, you wrote:

>-----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...)

Don't know. Alphanumeric order looks like this, highest to lowest:

1
10
100
1000

>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.

Goodness, you don't need any of that.

CAST ('123' AS SMALLINT) is all you need.

/heLen