CODEWITHSUNDEEP
sqlregexoracle-database
Ork
Ork

Reputation: 61

Oracle's SQL to extract values between two chars in a list

I have a list of value such as:

'aaa:data1!x,bbb:data2!y,cc:data3!z'

I need to extract values between ':' and '!' such as data1 data2 data3

I tried this: 

SELECT regexp_substr('aaa:data1!x,bbb:data2!y,cc:data3!z', 
                     '\:([^!]+)\!', 
                     1,
                     1,
                     NULL,
                     1) AS output
FROM dual;

But it returns me only data1

Upvotes: 1

Views: 60

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

You're on the right way, just need to send an iterated integers to the 4th argument of regexp_substr function

This gives vertical result :

with t(str) as
(
 select 'aaa:data1!x,bbb:data2!y,cc:data3!z' from dual
) 
select regexp_substr(str,
                     ':([^!]+)',
                     1,
                     level,
                     null,
                     1) as output                     
  from t
 connect by level <= regexp_count(str,'!');

 OUTPUT
 ------
 data1
 data2
 data3

This gives horizontal result :

with t(str) as
(
 select 'aaa:data1!x,bbb:data2!y,cc:data3!z' from dual
), t2 as
( 
 select regexp_substr(str,
                     ':([^!]+)',
                     1,
                     level,
                     null,
                     1) as output                     
   from t
 connect by level <= regexp_count(str,'!') 
)
select listagg(output,' ') within group (order by output) as output from t2  

 OUTPUT
 -----------------
 data1 data2 data3

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142958

Good, old SUBSTR + INSTR does the job:

SQL> with test (col) as
  2    (select 'aaa:data1!x,bbb:data2!y,cc:data3!z' from dual)
  3  select substr(col, instr(col, ':', 1, level) + 1,
  4                     instr(col, '!', 1, level) - instr(col, ':', 1, level) - 1
  5               ) result
  6  from test
  7  connect by level <= regexp_count(col, ':');

RESULT
----------------------------------
data1
data2
data3

SQL>

Upvotes: 1

Related Questions