Home » SQL & PL/SQL » SQL & PL/SQL » Need to get the comma separated value
Need to get the comma separated value [message #664944] Mon, 14 August 2017 06:49 Go to next message
rajeshsvnr
Messages: 2
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.
Re: Need to get the comma separated value [message #664946 is a reply to message #664944] Mon, 14 August 2017 07:13 Go to previous messageGo to next message
BlackSwan
Messages: 25633
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
Re: Need to get the comma separated value [message #664947 is a reply to message #664944] Mon, 14 August 2017 08:12 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2649
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.
Re: Need to get the comma separated value [message #664951 is a reply to message #664947] Mon, 14 August 2017 13:18 Go to previous messageGo to next message
EdStevens
Messages: 835
Registered: September 2013
Senior Member
While an interesting sql exercise, the data as presented violates every rule of data design. You should not be storing data as comma-separated lists.
Re: Need to get the comma separated value [message #665012 is a reply to message #664951] Thu, 17 August 2017 12:05 Go to previous message
rajeshsvnr
Messages: 2
Registered: June 2017
Junior Member
Thanks
Previous Topic: help with sql grouping
Next Topic: Help with PL SQL query - Analytical Function or group by
Goto Forum:
  


Current Time: Tue Sep 19 10:30:04 CDT 2017

Total time taken to generate the page: 0.01585 seconds