Oracle: find the largest number within one string

Question

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).

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!

is there a reason why you have to do this inside the database? In worst case scenario implement it in Java and use java stored proc. — ibre5041
– ibre5041, Commented Jul 9, 2018 at 17:38

Alex Poole · Accepted Answer · 2018-07-09 17:28:34Z

3

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

or

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

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

edited Jul 9, 2018 at 17:28

answered Jul 9, 2018 at 17:22

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

noobie2023 Over a year ago

Thank you! It's a great solution. However, since I have 1.8 billion rows in my table. It took forever to run the code... but still I've tested your code on 10 rows and it worked well. I was actually trying to find the largest (latest) numerical label for the data updating steps (log). Since each edition has its own number (label), I wanted a code to automatically find the latest label for me. So I will know the label (+1) for the next edition step.

Littlefoot · Accepted Answer · 2018-07-09 19:14:53Z

1

Alternatively (to Alex' answer), if there are multiple rows:

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>

answered Jul 9, 2018 at 19:14

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Collectives™ on Stack Overflow

Oracle: find the largest number within one string

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related