REGEXP_SUBSTR not able to process only current row

Question

(SELECT LISTAGG(EVENT_DESC, ',') WITHIN GROUP (ORDER BY EVENT_DESC) FROM EVENT_REF WHERE EVENT_ID IN 
                                    (   SELECT REGEXP_SUBSTR(AFTER_VALUE,'[^,]+', 1, level) FROM DUAL
                                        CONNECT BY REGEXP_SUBSTR(AFTER_VALUE, '[^,]+', 1, level) IS NOT NULL
                                    )
                                )

A table from which I am fetching AFTER_VALUE has values of integer which is comma seperated like

AFTER_VALUE data	Expected output
1	Event1
1,2	Event1,Event2
1,12,2,5	Event1,Event12,Event2,Event5
15,13	Event15,Event13

these are Ids in EVENT_REF table which have some description. I am trying to basically present ex. 1,2 as Event1, Event2 and send back from query. There are multiple events so using REPLACE would be very tedious.

When using above query I'm getting error as “ORA-01722: invalid number” whenever there is more than one value in AFTER_VALUE column Ex. if there exists only one id , then the query works but for values like 1,2 or 1,13 etc it throws invalid number error.

PS: The event names are not Event1,Event2 etc , I have just put for reference.

Are you sure that your subquery returns only strings that are convertible to number? You evaluate `connect by` subquery for each row, but the only thing you need from it is to check whether the number is in list or not, which can be checked with simple like: `where ',' || after_value || ',' like '%' || to_char(id, 'TM9') || '%'` — astentx, May 31 '21 at 21:11

score 1 · Answer 1 · answered May 31 '21 at 23:41

You don't even need regular expressions for this assignment. Standard string function replace() can do the same thing, and faster. You only need an extra 'Event' at the beginning of the string, since that one doesn't "replace" anything.

Like this: (note that you don't need the with clause; I included it only for quick testing)

with
  event_ref (after_value) as (
    select '1'        from dual union all
    select '1,2'      from dual union all
    select '1,12,2,5' from dual union all
    select '15,13'    from dual
  )
select after_value,
       'Event' || replace(after_value, ',', ',Event') as desired_output
from   event_ref
;

AFTER_VALUE  DESIRED_OUTPUT     
-----------  -----------------------------
1            Event1                   
1,2          Event1,Event2      
1,12,2,5     Event1,Event12,Event2,Event5
15,13        Event15,Event13

sorry i wasn't expecting people would take event desc as event1,event2 etc. they are different names and not what I've put. — Shalini Raj, Jun 01 '21 at 05:48

score 0 · Answer 2 · answered May 31 '21 at 21:36

0

There is no need to split and concatenate substrings, just use regexp_replace:

with EVENT_REF (AFTER_VALUE) as (
select '1' from dual union all
select '1,2' from dual union all
select '1,12,2,5' from dual union all
select '15,13' from dual
)
select regexp_replace(AFTER_VALUE,'(\d+)','Event\1') from EVENT_REF;

REGEXP_REPLACE(AFTER_VALUE,'(\D+)','EVENT\1')
-----------------------------------------------
Event1
Event1,Event2
Event1,Event12,Event2,Event5
Event15,Event13

answered May 31 '21 at 21:36

Sayan Malakshinov

8,492
1
19
27

sorry i wasn't expecting people would take event desc as event1,event2 etc. they are different names and not what I've put. – Shalini Raj Jun 01 '21 at 05:49

score 0 · Accepted Answer · answered Jun 01 '21 at 10:09

Ah,ok, looks, like you have other characters in your comma-separated list, so you can use this query:

with EVENT_REF(EVENT_ID,EVENT_DESC) as (
  select  1, 'Desc 1' from dual union all
  select  2, 'Desc 2' from dual union all
  select  3, 'Desc 3' from dual union all
  select  4, 'Desc 4' from dual union all
  select  5, 'Desc 5' from dual union all
  select 12, 'Desc12' from dual union all
  select 13, 'Desc13' from dual union all
  select 15, 'Desc15' from dual
)
select
(SELECT LISTAGG(EVENT_DESC, ',') 
   WITHIN GROUP (ORDER BY EVENT_DESC)
 FROM EVENT_REF 
 WHERE EVENT_ID IN 
          (   SELECT to_number(REGEXP_SUBSTR(AFTER_VALUE,'\d+', 1, level))
              FROM DUAL
              CONNECT BY level<=REGEXP_COUNT(AFTER_VALUE, '\d+')
          )
      )
from (
  select '1'        AFTER_VALUE from dual union all
  select '1,2'      AFTER_VALUE from dual union all
  select '1,12,2,5' AFTER_VALUE from dual union all
  select '15,13'    AFTER_VALUE from dual
);

PS. And do not forget that to_number has 'default on conversion error' now: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/TO_NUMBER.html

REGEXP_SUBSTR not able to process only current row

3 Answers3