Re: Comma delimited values into rows

From: <fitzjarrell_at_cox.net>
Date: 10 May 2006 18:54:15 -0700
Message-ID: <1147312455.840570.65100@g10g2000cwb.googlegroups.com>

johnv wrote:
> I have this string in a field called 'ID':
>
> 1000,1001,1002,...n
>
> In sql only, how can i write a view that treats the comma as a new row
> so that my result set reads as:
>
> ID
> -------
> 1000
> 1001
> 1002

SQL> select id
  2 from listest;

ID

---

1000,1001,1002,1003,1004

SQL> select translate(id, ',', chr(10)) from listest;

TRANSLATE(ID,',',CHR(10))

---

1000
1001
1002
1003
1004

SQL> David Fitzjarrell Received on Wed May 10 2006 - 20:54:15 CDT