Sql query - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Sql query

  1. #11
    Join Date
    Jun 2005
    Location
    UK
    Posts
    11
    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 !!!

  2. #12
    Join Date
    Jul 2005
    Posts
    4
    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
    awads.net

  3. #13
    Join Date
    Sep 2001
    Location
    UK
    Posts
    45
    Eddie Awad,
    Your solution will work only for single digit elements
    what if the string has elements like 'abc','qwerty' etc ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width