Thanks Hans

Was trying to avoid a SP but this looks like the beest way to do it.

Cheers
Rob







Hans wrote:

> Maybe this helps:
>
> Create PROCEDURE GETNUMVALUE( ALPHANUM VARCHAR(
> 255 ) )
> RETURNS ( NUMVALUE INTEGER )
> AS
> DECLARE VARIABLE i Integer;
> DECLARE VARIABLE NumVarChar VarChar (255);
> DECLARE VARIABLE c VarChar(1);
> BEGIN
>
> i = 1;
> NumVarChar = '';
> NumValue = Null;
>
> if (AlphaNum is not Null) then
> begin
>
> while (i <= Char_Length(AlphaNum)) do
> begin
> c = SubString(AlphaNum from i for 1);
>
> if (c in ('0','1','2','3','4','5','6','7','8','9')) then
> NumVarChar = NumVarChar || c;
>
> i = i + 1;
> end
>
> NumValue = NumVarChar;
> end
>
> Suspend;
> END
>
> and then
>
> select first 10
> Carrier_Id ,
> (select NumValue from GetNumValue(Carrier_Id))
> from Carrier where carrier_id is not null
>
> producing with an example database
>
> Carrier_ID NumValue
> CAR16 16
> CAR121 121
> A12B34 1234
>
> ----- Original Message -----
> From: "Robert martin" <rob@...>
> To: <firebird-support@yahoogroups.com>
> Sent: Sunday, April 19, 2009 7:21 PM
> Subject: [firebird-support] Numeric filtering of Character fields
>
>
>
>> Hi
>>
>> We have a Character field that frequently contains numeric entries but
>> sometimes also contains Alpha characters as well. We want to find the
>> largest numeric value in the entry in the field. Casting to numeric
>> works fine if no Characters are in the field, otherwise it goes bang.
>>
>> Is there a way (FB 2.01 ish with std functions only), to return just a
>> list of numeric character field values ?
>>
>> Thanks
>> Rob
>>
>>
>>
>> ------------------------------------
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>> Visit http://www.firebirdsql.org and click the Resources item
>> on the main (top) menu. Try Knowledgebase and FAQ links !
>>
>> Also search the knowledgebases at http://www.ibphoenix.com
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>> Yahoo! Groups Links
>>
>>
>>
>>
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>
>