OraFAQ Forum: SQL & PL/SQL » Need to get the comma separated value

Home » SQL & PL/SQL » SQL & PL/SQL » Need to get the comma separated value

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need to get the comma separated value [message #664944]

Mon, 14 August 2017 06:49

rajeshsvnr
Messages: 9
Registered: June 2017

Junior Member

Hi All,

Below is the table scripts

I have table tb_sno,

CREATE TABLE tb_sno (sno1 VARCHAR2 (100))
/

INSERT INTO tb_sno
VALUES ('10,4,8');

INSERT INTO tb_sno
VALUES ('3,15,16');

/

Need the output has :-
10
4
8
3
15
16

Can anyone help me out in this.

Report message to a moderator

Re: Need to get the comma separated value [message #664946 is a reply to message #664944]

Mon, 14 August 2017 07:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: Need to get the comma separated value [message #664947 is a reply to message #664944]

Mon, 14 August 2017 08:12

Solomon Yakobson
Messages: 3310
Registered: January 2010
Location: Connecticut, USA

Senior Member

SQL> select * 
  2    from tb_sno t,    
  3         xmltable (
  4                   '
  5                    for $c at $i in ora:tokenize($str,",")
  6                    where $i != 1
  7                    return $c
  8                   '
  9                   passing ',' || sno1 as "str"
 10                   columns
 11                     list_el number path '.') x
 12  /

SNO1          LIST_EL
---------- ----------
10,4,8             10
10,4,8              4
10,4,8              8
3,15,16             3
3,15,16            15
3,15,16            16
3,15,16             3
3,15,16            15
3,15,16            16

9 rows selected.

SQL>

SY.

Report message to a moderator