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