Sql query

Quote:

---

Originally Posted by WilliamR

Just for fun, try this (requires 9i, tested in 9.2.0.1):

Code:

---

SELECT str, EXTRACTVALUE(xt.column_value,'e') AS name
FROM  t
    , TABLE(XMLSEQUENCE
      ( EXTRACT
        ( XMLTYPE('<coll><e>' ||
                  REPLACE(t.str,',','</e><e>') ||
                  '</e></coll>')
        , '/coll/e') )) xt;

---

---

Brilliant !!!

Quote:

---

Originally Posted by dinesh_hm

I have an input string ('a','b','c')
Is there any way i can get the follg o/p using SQL (8i or 91 or 10g) (No PL/SQL)
'a'
'b'
'c'

I need the result to be displayed as 3 records
instead of 1 record

The no of columns in string may vary

---

What a coincidence! I blogged about the same problem today. Try this:

Code:

---

select substr (replace ('a,b,c', ','), rownum, 1)
from all_objects
where rownum <= length (replace ('a,b,c', ','));

---

Eddie Awad,
Your solution will work only for single digit elements
what if the string has elements like 'abc','qwerty' etc ?