From: "Lester Caine" <lester@...>

> I have a data source which has 'age' as a field. This is normally years,
> but also includes some text (3 mths, infant, 2 d, etc) There are not
> many text entries, and a lot I've filtered, but I need a check ideally
> in the CASE statement that pulls out the 'clean' numeric year entries,
> so I'm just left with the odd ones.

Perhaps SP like this ...
(expects that the string starts with number)

CREATE OR ALTER PROCEDURE P (str VARCHAR(20)) RETURNS (ret INTEGER) AS
DECLARE VARIABLE tmp VARCHAR(20);
BEGIN
/* Skip leading spaces */
WHILE (str LIKE ' %') DO
str = SUBSTRING(str FROM 2);

/* Find the longest numeric string */
tmp = '';
BEGIN
WHILE (str <> '') DO BEGIN
tmp = tmp || SUBSTRING(str FROM 1 FOR 1);
ret = tmp;
str = SUBSTRING(str FROM 2);
END
WHEN ANY DO
/* If the number is followed by anything not starting Y(ear), return NULL */
IF (SUBSTRING(str FROM 1 FOR 1) NOT IN ('','y','Y'))
THEN ret = NULL;
END

SUSPEND;
END


Ivan
http://www.volny.cz/iprenosil/interbase/