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