Using Oracle 9i, how can I extract the first number from a string?

Question

I need to extract the first integer number found in a column of a database. Unfortunately, I am stuck using data from an Oracle 9i database, which doesn't support regex.

Some data examples:

'Count 1,2,3...' should return '1' 
'3.1415 is pi' should return '3' 
'I saw 11 Olds 442s today' should return '11' 
'Part number 12,345' should return '12' 
'Order 5 100 piece kits' should return '5'
'ABcdEFg' should return ''

I cannot figure out how to extract these numbers from these strings using Oracle 9i.

In Oracle 10g+ I could use REGEXP_SUBSTR(my_data, '([0-9]+)'), like so:

SELECT name, REGEXP(address, '([0-9]+)') as street_num FROM people;

How can I accomplish this?

Answer 1

Oh well. The simplest "solution" is to upgrade, if possible.

Otherwise, see if such a function helps.

SQL> create or replace function f_digit (par_string in varchar2)
  2    return varchar2
  3  is
  4    l_chr  varchar2(1);
  5    retval varchar2(200);
  6  begin
  7    for i in 1 .. length(par_string) loop
  8      l_chr := substr(par_string, i, 1);
  9
 10      if ascii(l_chr) between 48 and 57 then
 11         retval := retval || l_chr;
 12      end if;
 13
 14      if retval is not null and ascii(l_chr) not between 48 and 57 then
 15         exit;
 16      end if;
 17    end loop;
 18    return retval;
 19  end;
 20  /

Function created.

Testing:

SQL> select col, f_digit(col) result
  2  from (select 'count 1,2,3...' col        from dual union all
  3        select '3.1415 is pi'              from dual union all
  4        select 'I saw 11 Olds 442s today'  from dual union all
  5        select 'Part number 12,345'        from dual union all
  6        select 'Order 5 100 piece kits'    from dual union all
  7        select 'ABcdEFg'                   from dual
  8       );

COL                      RESULT
------------------------ ----------
count 1,2,3...           1
3.1415 is pi             3
I saw 11 Olds 442s today 11
Part number 12,345       12
Order 5 100 piece kits   5
ABcdEFg

6 rows selected.

SQL>