使用Oracle 9i，我如何从字符串中提取第一个数字?

Question

我需要提取在数据库列中找到的第一个整数。不幸的是，我被困在使用Oracle 9i数据库的数据，它不支持正则表达式。

一些数据示例:

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

我不知道如何使用Oracle 9i从这些字符串中提取这些数字。

在Oracle 10g+中，我可以使用REGEXP_SUBSTR(my_data， '([0-9]+)')，如下所示:

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

我怎样才能做到这一点?

Answer 1

好吧。最简单的"解决方案"是升级，如果可能的话。

否则，看看这个函数是否有帮助。

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.

测试:

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>