Oracle:在一个字符串中查找最大的数字

您可以将字符串标记为所有的数字组件,然后找到最大值:

select max(to_number(
    regexp_substr('sdfAB98:(hjkl,)AB188(uiop)uuuAB78:jknd', '(\d+)', 1, level))
  ) as max_value
from dual
connect by regexp_substr('sdfAB98:(hjkl,)AB188(uiop)uuuAB78:jknd', '(\d+)', 1, level)
  is not null;

 MAX_VALUE
----------
       188

或

select max(to_number(
    regexp_substr('sdfAB98:(hjkl,)AB188(uiop)uuuAB78:jknd', '(\d+)', 1, level, null, 1))
  ) as max_value
from dual
connect by level <= regexp_count('sdfAB98:(hjkl,)AB188(uiop)uuuAB78:jknd', '\d+');

 MAX_VALUE
----------
       188

如果需要从多行中获取值,则需要connect by来匹配id,还需要包含对不确定函数的引用以防止循环;cte中有两个值:

with your_table (id, str) as (
  select 1, 'sdfAB98:(hjkl,)AB188(uiop)uuuAB78:jknd' from dual
  union all select 2, '123abc456abc78d9' from dual
)
select id, max(to_number(regexp_substr(str, '(\d+)', 1, level, null, 1))) as max_value
from your_table
connect by prior id = id
and prior dbms_random.value is not null
and level <= regexp_count(str, '\d+')
group by id;

        ID  MAX_VALUE
---------- ----------
         1        188
         2        456

或者(对亚历克斯的回答),如果有多行:

SQL> with your_table (id, str) as (
  2    select 1, 'sdfAB98:(hjkl,)AB188(uiop)uuuAB78:jknd' from dual
  3    union all select 2, '123abc456abc78d9' from dual
  4  )
  5  select id, max(to_number(regexp_substr(str, '\d+', 1, column_value))) max_num
  6  from your_table,
  7       table(cast(multiset(select level from dual
  8                           connect by level <= regexp_count(str, '\d+')
  9                          ) as sys.odcinumberlist))
 10  group by id;

        ID    MAX_NUM
---------- ----------
         1        188
         2        456

SQL>