Retrieve certain number from data set in Oracle 10g

Question

1. <0,0><120.96,2000><241.92,4000><362.88,INF>
2. <0,0><143.64,2000><241.92,4000><362.88,INF>
3. <0,0><125.5,2000><241.92,4000><362.88,INF>
4. <0,0><127.5,2000><241.92,4000><362.88,INF>

Above is the data set I have in Oracle 10g. I need output as below

1. 120.96
2. 143.64
3. 125.5
4. 125.5

the output I want is only before "comma" (120.96). I tried using REGEXP_SUBSTR but I could not get any output. It will be really helpful if someone could provide effective way to solve this

_I tried using REGEXP_SUBSTR_ **How?** – Rahul May 27 '17 at 19:30 — Rahul, May 27 '17 at 19:30
REGEXP_REPACE should work fine here, try. – Wiktor Stribiżew May 27 '17 at 19:56 — Wiktor Stribiżew, May 27 '17 at 19:56

score 0 · Accepted Answer · answered May 27 '17 at 19:56

0

Here is one method that first parses out the second element and then gets the first number in it:

select regexp_substr(regexp_substr(x, '<[^>]*>', 1, 2), '[0-9.]+', 1, 1)

Another method just gets the third number in the string:

select regexp_substr(x, '[0-9.]+', 1, 3)

answered May 27 '17 at 19:56

Gordon Linoff

1,242,037
58
646
786

@GordonLinoff some overrated experienced persons love to down vote – Zaid Mirza Mar 08 '18 at 13:40

RKRC · Answer 2 · 2017-05-28T18:44:52.873

Here is an approach without using Regexp. Find the index of second occurrence of '<'. Then find the second occurrence of ',' use those values in substring.

    with
    data as
    (
    select '<0,0><120.96,2000><241.92,4000><362.88,INF>' x from dual
    UNION ALL
    select '<0,0><143.64,2000><241.92,4000><362.88,INF>' x from dual
    UNION ALL
    select '<0,0><125.5,2000><241.92,4000><362.88,INF>' from dual
    )
    select substr(x, instr(x,'<',1,2)+1, instr(x,',',1,2)- instr(x,'<',1,2)-1)
    from data

Approach Using Regexp: Identify the 2nd occurence of numerical value followed by a comma Then remove the trailing comma.

    with
    data as
    (
    select '<0,0><120.96,2000><241.92,4000><362.88,INF>' x from dual
    UNION ALL
    select '<0,0><143.64,2000><241.92,4000><362.88,INF>' x from dual
    UNION ALL
    select '<0,0><125.5,2000><241.92,4000><362.88,INF>' from dual
    )
    select 
    trim(TRAILING ',' FROM regexp_substr(x,'[0-9.]+,',1,2))
    from data

Gary_W · Answer 3 · 2017-05-30T15:44:25.563

This example uses regexp_substr to get the string contained within the 2nd occurance of a less than sign and a comma:

SQL> with tbl(id, str) as (
     select 1, '<0,0><120.96,2000><241.92,4000><362.88,INF>' from dual union
     select 2, '<0,0><143.64,2000><241.92,4000><362.88,INF>' from dual union
     select 3, '<0,0><125.5,2000><241.92,4000><362.88,INF>'  from dual union
     select 4, '<0,0><127.5,2000><241.92,4000><362.88,INF>'  from dual
   )
   select id,
          regexp_substr(str, '<(.*?),', 1, 2, null, 1) value
   from tbl;

        ID VALUE
---------- -------------------------------------------
         1 120.96
         2 143.64
         3 125.5
         4 127.5

EDIT: I realized the OP specified 10g and the regexp_substr example I gave used the 6th argument (subgroup) which was added in 11g. Here is an example using regexp_replace instead which should work with 10g:

SQL> with tbl(id, str) as (
        select 1, '<0,0><120.96,2000><241.92,4000><362.88,INF>' from dual union
        select 2, '<0,0><143.64,2000><241.92,4000><362.88,INF>' from dual union
        select 3, '<0,0><125.5,2000><241.92,4000><362.88,INF>'  from dual union
        select 4, '<0,0><127.5,2000><241.92,4000><362.88,INF>'  from dual
      )
      select id,
             regexp_replace(str, '^(.*?)><(.*?),.*$', '\2') value
      from tbl;

        ID VALUE
---------- ----------
         1 120.96
         2 143.64
         3 125.5
         4 127.5

SQL>

Retrieve certain number from data set in Oracle 10g

3 Answers3