CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle11gr2listagg
user9892866
user9892866

Reputation:

making comma separated string in Oracle

How can we convert 12345 into 1,2,3,4,5 .

I can do the reverse by using replace command and i can replace comma by null. But I am not able to do the above. Could you please help on the same.

Thanks in Advance

Upvotes: 0

Views: 152

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

If you have a fixed template with consecutive, one-digit numbers like 1234 or 12345 or 123456789

you may try the following by using listagg function of Oracle :

with t as
(
 select '12345' as col from dual 
)
select listagg(level,',') within group (order by level) as str
   from t
connect by level <= length(col);

STR
---------
1,2,3,4,5

SQL Fiddle Demo 1

OR

For more generalized solution, use the following :

with t as
(
 select 'abcde' as col from dual 
)
select listagg(substr(col,level,1),',') within group (order by level) as str
   from t
connect by level <= length(col);

STR
---------
a,b,c,d,e

SQL Fiddle Demo 2

Upvotes: 2

user330315
user330315

Reputation:

You can use regexp_replace():

rtrim(regexp_replace('12345', '([0-9])', '\1,'), ',')

The rtrim() is necessary because the last digit is also replaced.

Online example: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=724adecda03305b281ad3bf0f380ca58

Upvotes: 4

Related Questions