How to select values within a column

Question

Apologies for sharing little information than needed. Should have provided the entire thing. Here's it.

we are sending pre-check patching information from many DB's via a tool to some logs on another server, that server has the main DB which will store this info for comparing later on. We are using the tags "<>" to make our statements unique for identification.

The logfile info will look like this:

StepLog:Info: dbname instance_name hostname objnames and so on..

From the database on that server, how do I extract only the values between the tags to store/insert in a table, and skip the tags. Since the info will me from multiple DB's cannot hard quote info.

Thanks.

Answer 1

Based on the solution with regexp split you may run following query.

Note however, that you must know the maximal number of the resulting columns in advance.

 with t1 as (select 1 rn, 'Orcl, orcl, linux box, Pass, tablespace_name1, tablespace_name2' col from dual union all
              select 2 rn, 'Orcl2, orcl2, linux box2, Pass2, tablespace_name12, tablespace_name22' col from dual),
      t2 as (select  rownum colnum from dual connect by level <= 6 /* (max) number of columns */)
 select t1.rn, t2.colnum, rtrim(ltrim(regexp_substr(t1.col,'[^,]+', 1, t2.colnum)))  col  from t1, t2 
 where regexp_substr(t1.col, '[^,]+', 1, t2.colnum) is not null
 order by rn,colnum;

This gives you the row, column and key view:

    RN     COLNUM COL                                                                 
 ----- ---------- ------------------ 
     1          1 Orcl                                                                  
     1          2 orcl                                                                  
     1          3 linux box                                                             
     1          4 Pass                                                                  
     1          5 tablespace_name1                                                      
     1          6 tablespace_name2                                                      
     2          1 Orcl2                                                                 
     2          2 orcl2                                                                 
     2          3 linux box2                                                            
     2          4 Pass2                                                                 
     2          5 tablespace_name12                                                     
     2          6 tablespace_name22  

You may use the PIVOT query to get the result in flat table

 with t1 as (select 1 rn, 'Orcl, orcl, linux box, Pass, tablespace_name1, tablespace_name2' col from dual union all
              select 2 rn, 'Orcl2, orcl2, linux box2, Pass2, tablespace_name12, tablespace_name22' col from dual),
      t2 as (select  rownum colnum from dual connect by level <= 6 /* (max) number of columns */),
      t3 as (select t1.rn, t2.colnum, rtrim(ltrim(regexp_substr(t1.col,'[^,]+', 1, t2.colnum)))  col  from t1, t2 
      where regexp_substr(t1.col, '[^,]+', 1, t2.colnum) is not null)
 select * from t3
 PIVOT (max(col) col  for (colnum) in 
 (1 as "1",
  2 as "2",
  3 as "3",
  4 as "4",
  5 as "5",
  6 as "6"))
 order by rn;


    RN 1_COL     2_COL     3_COL     4_COL     5_COL              6_COL   
 ----- --------- --------- --------- --------- ---------          ---------
     1 Orcl      orcl      linux box Pass      tablespace_name1   tablespace_name2 
     2 Orcl2     orcl2     linux box2Pass2     tablespace_name12  tablespace_name22  

Again, as you see you must list all the resulting columns in the query.