CODEWITHSUNDEEP
oracle-databaseregexp-substr
Shalini Raj
Shalini Raj

Reputation: 307

REGEXP_SUBSTR not able to process only current row

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

Upvotes: 0

Views: 61

Answers (3)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

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

Upvotes: 0

user5683823
user5683823

Reputation:

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

Upvotes: 1

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

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

Upvotes: 0

Related Questions