string processing help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: string processing help

  1. #1
    Join Date
    Feb 2002
    Posts
    267

    string processing help

    I am trying to write a procedure to Cut the string and insert the values into a table. But not able to really succeed.

    Can somebody help me in improvising this code.
    ServerString is like

    data1;data2;data3,data01;;data03
    (each row separated by , and column data separated by ; )



    This string should go into table as:

    PHP Code:
    col1       col2         col3
    -------------------------------
    data1     data2       data3
    data01                  data03 



    Create or replace procedure ProcessString (ServerString IN Varchar2)
    Is
    v_serverId Server_Detail.Server_Id%type;

    Cursor get_vals is Select
    regexp_substr(astring,'[^;]+',1,1) v1,
    regexp_substr(astring,'[^;]+',1,2) v2,
    regexp_substr(astring,'[^;]+',1,3) v3,
    regexp_substr(astring,'[^;]+',1,4) v4,
    regexp_substr(astring,'[^;]+',1,5) v5,
    regexp_substr(astring,'[^;]+',1,6) v6
    from (select ServerString astring from dual);




    Begin

    for a in get_vals loop
    Begin

    dbms_output.put_line('1 value:'||a.v1);
    dbms_output.put_line('2 value:'||a.v2);
    dbms_output.put_line('3 value:'||a.v3);
    dbms_output.put_line('4 value:'||a.v4);
    dbms_output.put_line('5 value:'||a.v5);

    End;
    end loop;

    End;
    Last edited by Sonia; 07-28-2010 at 09:53 AM.

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    Code:
    with t as (select regexp_substr(a1, '[^,]+', 1, level) str
               from t2
               connect by regexp_substr(a1, '[^,]+', 1, level) is not null)
    select regexp_substr(str, '[^;]*') col1
    ,      trim(';' from regexp_substr(str, ';[^;]*;')) col2
    ,      regexp_substr(str, '[^;]*$') col3
    from t

  3. #3
    Join Date
    Feb 2002
    Posts
    267
    Excellent ebrain.
    Thx.

    Just wondering if this can be modified for variable columns.

    Like i have string length varying.


    For eg.,
    i have strings of columns 2,3 and 4

    eg
    data1;data2;data3,data01;;data03
    data1;data2,dataA;dataB

    If some data is missing then it will be blank. like.,
    data1;;data2
    But each string will have fixed number of columns.
    ie.,
    3;3;3,3;3;3
    2;2,2;2

    Instead of writing 3 different queries, can i modify the existing one.
    Last edited by Sonia; 07-29-2010 at 10:33 AM.

  4. #4
    Join Date
    Apr 2006
    Posts
    377
    Code:
    SQL> select * from t2;
    
    A1
    --------------------------------------------------------
    data1;data2,dataA;dataB
    ;bbb,aaa;
    abc1;abc2;,zzz1;;zzz3,;;yyy3,;mmm2;,data01;data02;data03
    
    SQL> with t as (select distinct rn, regexp_substr(a1, '[^,]+', 1, level) str
      2             from (select rownum rn, t2.*
      3                   from t2)
      4             connect by regexp_substr(a1, '[^,]+', 1, level) is not null
      5            )
      6  select regexp_substr(str, '[^;]*') col1
      7  ,      translate(regexp_substr(str, ';[^;,]*(;|,|$)'),'1;,','1') col2
      8  ,      trim(regexp_substr(replace(str,';',' ; '), '[^;]+', 1, 3)) col3
      9  from t
     10  order by rn;
    
    COL1       COL2       COL3
    ---------- ---------- ----------
    data1      data2
    dataA      dataB
               bbb
    aaa
    abc1       abc2
    zzz1                  zzz3
               mmm2
                          yyy3
    data01     data02     data03
    If you are only going to manipulate one string at a time, then the following will suffice:

    Code:
    SQL> select * from t2;
    
    A1
    ------------------------------------------------------------------------------
    abc1;abc2;,zzz1;;zzz3,;;yyy3,;mmm2;,data01;data02;data03
    
    SQL> with t as (select regexp_substr(a1, '[^,]+', 1, level) str
      2             from t2
      3             connect by regexp_substr(a1, '[^,]+', 1, level) is not null
      4            )
      5  select regexp_substr(str, '[^;]*') col1
      6  ,      translate(regexp_substr(str, ';[^;,]*(;|,|$)'),'1;,','1') col2
      7  ,      trim(regexp_substr(replace(str,';',' ; '), '[^;]+', 1, 3)) col3
      8  from t;
    
    COL1       COL2       COL3
    ---------- ---------- ----------
    abc1       abc2
    zzz1                  zzz3
                          yyy3
               mmm2
    data01     data02     data03
    If you are doing a huge number of records, then PL/SQL may be more performant.
    Last edited by ebrian; 08-01-2010 at 10:26 PM. Reason: Added additional option.

  5. #5
    Join Date
    Feb 2002
    Posts
    267
    excellent.
    Thx ebrain.

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