CODEWITHSUNDEEP
oracle-databaseoracle11g
Edinei Raduvanski
Edinei Raduvanski

Reputation: 55

how to separate comma separated values in oracle 11G

I need to separate the value of a column by commas.

Example: BCDEY; I need to convert to a B, C, D, E, Y. Follows the "select": 

SELECT CDGRUPOCONDICAO FROM TBINTCLIENTE;

Upvotes: 3

Views: 961

Answers (3)

Aramillo
Aramillo

Reputation: 3216

You can try also this:

with cad as  
(select 'BCDEY' cad from dual)
select regexp_replace (regexp_replace(cad,'(.)','\1, '),', $','') cad_comma from cad;

Upvotes: 2

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

An other solution, using recursive subquery factoring (hence, assuming oracle >= 11g release 2):

with testdata as (select 1 id, 'BCDEY' str from dual union all
                  select 2, 'ABC' from dual),
--                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--                 replace that subquery by your actual query
     splited(c,r,id,lvl) as (
                  select '' c, str r, id, 0 lvl from testdata
                  union all
                  select substr(r, 1, 1) c,
                         substr(r, 2) r,
                         id,
                         lvl+1
                  from splited
                  where r is not null)

select listagg(c, ', ') within group(order by lvl)
from splited
group by (id)

Producing:

LISTAGG(C,',')WITHINGROUP(ORDERBYLVL)
B, C, D, E, Y
A, B, C

See http://sqlfiddle.com/#!4/d41d8/38971

Upvotes: 1

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

Something like that maybe?

with testdata as (select 'BCDEY' str from dual)

select listagg(c, ', ') within group(order by lvl)
from (
  select substr(str, level, 1) c,
         level lvl
  from testdata
  connect by level <= length(str)
  )

Producing:

B, C, D, E, Y

Here, the subquery split the string character by character. Then the outer listagg re-assemble the items by joining them with ', '.

Upvotes: 1

Related Questions