Clever Geek Handbook

How to separate comma separated values ​​in oracle 11G

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

Example: BCDEY

; I need to convert to B, C, D, E, Y

. Executes "select":

SELECT CDGRUPOCONDICAO FROM TBINTCLIENTE;
+3


source to share


3 answers


You can also try this:



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


source


Something like this 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)
  )

Production:



B, C, D, E, Y

Here the subquery splits the string character with a character. The outer then listagg

reassembles the elements by attaching them to ', '

.

+1


source


Another 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)

Production:

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

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

+1


source






More articles:


All Articles