DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Sql query

  1. #1
    Join Date
    Sep 2001
    Location
    UK
    Posts
    45

    Sql query

    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

    Thnx in advance

  2. #2
    Join Date
    Jun 2005
    Location
    UK
    Posts
    11
    A strange requirement for no PL/SQL, but here's a SQL-only solution, though you will need a SQL collection type. What I've done is to determine the number of delimited elements in each string and then generate a row per element "on the fly". Using a combination of INSTR, SUBSTR and the element number, it is possible to parse out the individual values.

    Note that in the example, I've commented out a REGEXP_REPLACE function ( 10g ). Including this was bombing my session. YMMV.

    Code:
    SQL> CREATE TABLE t
      2  ( str VARCHAR2(30) );
    
    Table created.
    
    SQL>
    SQL> INSERT INTO t VALUES ( 'X,Y,Z' );
    
    1 row created.
    
    SQL> INSERT INTO t VALUES ( 'XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG' );
    
    1 row created.
    
    SQL>
    SQL> CREATE TYPE number_ntt AS TABLE OF NUMBER;
      2  /
    
    Type created.
    
    SQL>
    SQL> WITH ilv AS
      2     (
      3      SELECT str || ',' AS str
      4      ,      LENGTH(
      5                TRANSLATE(
      6                   UPPER( str ), ',ABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890!', ',' ) ) + 1 AS no_of_elements
      7      --,      LENGTH( REGEXP_REPLACE( s, '[[:alnum:]]' ) ) + 1 AS no_of_elements
      8      FROM   t
      9     )
     10  SELECT RTRIM(str,',') AS original_string
     11  ,      SUBSTR( str, start_pos, (next_pos-start_pos) ) AS single_element
     12  ,      element_no
     13  FROM  (
     14         SELECT ilv.str
     15         ,      nt.column_value AS element_no
     16         ,      INSTR(
     17                   ilv.str,
     18                   ',',
     19                   DECODE( nt.column_value, 1, 0, 1 ),
     20                   DECODE( nt.column_value, 1, 1, nt.column_value-1 ) ) + 1 AS start_pos
     21         ,      INSTR(
     22                   ilv.str,
     23                   ',',
     24                   1,
     25                   DECODE( nt.column_value, 1, 1, nt.column_value ) ) AS next_pos
     26         FROM   ilv
     27         ,      TABLE(
     28                   CAST(
     29                      MULTISET(
     30                         SELECT ROWNUM FROM dual CONNECT BY ROWNUM < ilv.no_of_elements
     31                         ) AS number_ntt ) ) nt
     32        );
    
    ORIGINAL_STRING                 SINGLE_ELEMENT                  ELEMENT_NO
    ------------------------------- ------------------------------- ----------
    X,Y,Z                           X                                        1
    X,Y,Z                           Y                                        2
    X,Y,Z                           Z                                        3
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG  XXX                                      1
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG  Y                                        2
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG  ZZ                                       3
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG  AAAAA                                    4
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG  B                                        5
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG  CCC                                      6
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG  D                                        7
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG  E                                        8
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG  F                                        9
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG  GGG                                     10
    
    13 rows selected.
    Regards
    Adrian

  3. #3
    Join Date
    Nov 2001
    Location
    The Netherlands
    Posts
    19
    Hi,

    If you have a table containing at least as many records as the input values, you can use the following query

    Code:
    select substr(:x,begin,end-begin)
    from (select pos+1 begin,nvl(lead(pos) over (order by pos),length(:x)+1) end
         from (select instr(:x,',',1,rownum) pos 
          from dba_objects) 
       where rownum <= length(:x)-length(replace(:x,','))+1
         );
    SQL> exec :x := 'a,b,c,d,e,f,g,h'

    PL/SQL-procedure is geslaagd.

    SQL> select substr(:x,begin,end-begin)
    2 from (select pos+1 begin,nvl(lead(pos) over (order by pos),length(:x)+1) end
    3 from (select instr(:x,',',1,rownum) pos
    4 from dba_objects)
    5 where rownum <= length(:x)-length(replace(:x,','))+1
    6 );

    SUBSTR(:X,BEGIN,END-BEGIN)
    --------------------------------------------------------------------------------
    a
    b
    c
    d
    e
    f
    g
    h

    8 rijen zijn geselecteerd.

    Regards,

    Arian

  4. #4
    Join Date
    Jun 2005
    Location
    UK
    Posts
    11
    Arian,

    I think the OP has a whole table full of these strings and as such, you need to consider a join.

    Regards
    Adrian

  5. #5
    Join Date
    Nov 2001
    Location
    The Netherlands
    Posts
    19
    Adrian,

    I don't see why I need a join. I just need a table with sufficient rows. And in 9i and up, I could use dual with a connect by clause to generate sufficient rows.

    Regards,

    Arian

  6. #6
    Join Date
    Jun 2005
    Location
    UK
    Posts
    11
    If you look at my example, I have a table "T" with more than one string of different sizes, as specified by the OP. You need to consider how you would replace your :x bind variable with a SELECT from the table of strings. It's less simple when you need to extract the elements from more than one string.

    Regards
    Adrian

  7. #7
    Join Date
    Nov 2001
    Location
    The Netherlands
    Posts
    19
    Ok. I didn't understand from the question that there is a table.

    Unfortunately, I can't run your query, since we're still on 8i. So I had to try something else.

    Code:
     select str, substr(str,begin,end-begin) element
     from (select str,pos+1 begin,nvl(lead(pos) over (partition by tri order by pos),length(str)+1) end
     	   from (select a.str, instr(a.str,',',1,o.rn) pos, a.tri
     	   		 from (select t.str,length(t.str)-length(replace(t.str,','))+1 num_el,t.rowid tri  
    			 	   from t
    			      ) a
     			 ,    (select rownum rn, object_id 
    			       from dba_objects) o 
    			 where o.rn<=a.num_el
    			 )
      	   );
    Code:
    SQL>  CREATE TABLE t
      2   ( str VARCHAR2(30) );
    
    Tabel is aangemaakt.
    
    SQL> INSERT INTO t VALUES ( 'X,Y,Z' );
    
    1 rij is aangemaakt.
    
    SQL> INSERT INTO t VALUES ( 'XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG' );
    
    1 rij is aangemaakt.
    
    SQL>  select str, substr(str,begin,end-begin) element
      2   from (select str,pos+1 begin,nvl(lead(pos) over (partition by tri order by pos),length(str)+1)
     end
      3       from (select a.str, instr(a.str,',',1,o.rn) pos, a.tri
      4          from (select t.str,length(t.str)-length(replace(t.str,','))+1 num_el,t.rowid tri  
      5          from t
      6           ) a
      7       ,    (select rownum rn, object_id 
      8            from dba_objects) o 
      9      where o.rn<=a.num_el
     10      )
     11        );
    
    STR                            ELEMENT
    ------------------------------ ------------------------------
    X,Y,Z                          X
    X,Y,Z                          Y
    X,Y,Z                          Z
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG XXX
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG Y
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG ZZ
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG AAAAA
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG B
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG CCC
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG D
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG E
    
    STR                            ELEMENT
    ------------------------------ ------------------------------
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG F
    XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG GGG
    
    13 rijen zijn geselecteerd.
    Regards,

    Arian

    Edit: I realized later, that I used dba_objects as a matter of habit. Replace that with all_objects. In your case all_objects, or a custom-made table with sufficient rows would suffice. From 9i on, dual with a connect by is a safe replacement.
    Last edited by Alien; 07-13-2005 at 01:21 PM. Reason: Creatures of habit

  8. #8
    Join Date
    Nov 2001
    Location
    The Netherlands
    Posts
    19

    To Adrian: Wonder about 9i

    Hi Adrian,

    Hope you'll be able to follow up on this reply.
    I tested your script on my own server. And I received an ORA-3113.

    So I was wondering what versions you used to test your script. I tested on 8.1.7.4 and 9.2.0.5.

    If you managed to run it on <=9.2.0.5, I'd like to file a bug with Oracle Support.

    Regards,

    Arian

    P.S. Wonder when people get confused about our names?
    Last edited by Alien; 07-13-2005 at 02:22 PM.

  9. #9
    Join Date
    Jun 2005
    Location
    UK
    Posts
    11
    Arian,

    The query posted above was tested on 10.1.0.3 only, but it is all 9i syntax. I was getting the ORA-3113 when I included the REGEXP_REPLACE. But, I have an 8i version that I also wrote and forgot to post ( the OP asked for all versions ). You could perhaps test this:-

    Code:
    SELECT RTRIM( str,',' ) AS original_string
    ,      SUBSTR( str, start_pos, (next_pos-start_pos) ) AS single_element
    ,      element_no
    FROM  (
           SELECT ilv.str
           ,      nt.column_value AS element_no
           ,      INSTR( 
                     ilv.str,
                     ',', 
                     DECODE( nt.column_value, 1, 0, 1 ),
                     DECODE( nt.column_value, 1, 1, nt.column_value-1 ) ) + 1 AS start_pos
           ,      INSTR( 
                     ilv.str, 
                     ',',
                     1,
                     DECODE( nt.column_value, 1, 1, nt.column_value ) ) AS next_pos
           FROM  (
                  SELECT str || ',' AS str
                  ,      LENGTH( 
                            TRANSLATE( 
                               UPPER( str ),
                               ',ABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890', ',' ) ) + 1 AS no_of_elements
                  FROM   t
                 ) ilv
           ,      TABLE( 
                     CAST( 
                        MULTISET( 
                           SELECT ROWNUM FROM all_objects WHERE ROWNUM <= ilv.no_of_elements
                           ) AS number_ntt ) ) nt
          )
    Regards,
    Arian, I mean, Adrian...

  10. #10
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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;
    Last edited by WilliamR; 07-17-2005 at 05:48 PM.

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