Oracle:在一个字符串中找到最大的数字 [英] Oracle: find the largest number within one string

问题描述

我在asdfAB98:(hjkl,)AB188(uiop)uuuAB78:jknd之类的表的列中有一些字符串.我想知道如何在每一行的这样的字符串中提取最大的数字.例如，此处最大的数字是188(在188，98和78中).

I have some strings within a column of a table like asdfAB98:(hjkl,)AB188(uiop)uuuAB78:jknd. I am wondering how may I extract the largest number within such a string in each row. For example, here the largest number is 188 (out of 188, 98 and 78).

由于我感兴趣的数字始终在AB之后，因此我在考虑使用regexp_substr.不幸的是，我不确定如何使其输出多行，以便可以使用max子句. PLSQL语言也很好.如果您有想法，请给我看一个简单的例子.预先谢谢你！

Since the numbers I'm interested in are always right after AB, I was thinking about using regexp_substr. Unfortunately, I'm not sure how to have it output multiple rows so that I can use max clause. PLSQL language would be great as well. Please show me a simple example if you have an idea. Thank you in advance!

推荐答案

您可以将字符串标记为所有数字分量，然后找到最大值:

You could tokenize the string into all its number components, and then find the maximum:

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中有两个值:

If you need to get values from multiple rows you need the connect-by to match the IDs, and also need to include a reference to a non-deterministic function to prevent looping; with two values in a 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

这篇关于Oracle:在一个字符串中找到最大的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！