title: Convert one column by regex into two columns on Oracle 11.1g
body: <p>I have this string in column (Oracle 11.1g) (actually one value but formatted to make things clearer):</p>
  
  <pre><code>#(#GLCTPAbstractSchedule 
      #(99 37800 nil) 
      #(nil nil nil) 
      #(nil nil nil) 
      #(10 45000 nil) 
      #(99 45000 nil) 
      #(nil nil nil) 
      #(nil nil nil) 
      #(99 55800 nil) 
      #(10 57900 nil) 
      #(99 70200 nil))
  </code></pre>
  
  <p>and I need this result in two columns as follows</p>
  
  <pre><code>99 37800
  10 45000
  99 45000
  99 55800
  99 70200
  </code></pre>
  
  <p>I am trying use reg ex but now without any success.
  Anyone could help?
  Thanks</p>
  
  <p>Finally I solved it</p>
  
  <pre><code>select REGEXP_SUBSTR(test, '[^ ]+', 1, 1) col_one,
         REGEXP_SUBSTR(test, '[^ ]+', 1, 2) col_two
         from (
          SELECT REGEXP_SUBSTR('
              #(#GLCTPAbstractSchedule#(99 37800 nil) #(nil nil nil) #(nil nil nil) #(10 45000 nil) #(99 45000 nil) #(nil nil nil) #(nil nil nil) #(99 55800 nil) #(10 57900 nil) #(99 70200 nil))'
              ,'[^#(]+[0-9]',1,ROWNUM) test
          FROM dual connect by rownum &lt;= REGEXP_count('#(#GLCTPAbstractSchedule #(99 37800 nil) #(nil nil nil) #(nil nil nil) #(10 45000 nil) #(99 45000 nil) #(nil nil nil) #(nil nil nil) 
              #(99 55800 nil) #(10 57900 nil) #(99 70200 nil))', '\(\d')
  );
  </code></pre>
  
  <p>Thank for help</p>
  
  <p>Unfortunately it is not exactly what I want, cause I have table with many of these strings #(#GLCTPAbstractSchedule.....</p>
  
  <p>I solved it this, but enyone know about better solution?</p>
  
  <pre><code> with
          tours as (
          SELECT * FROM (
              SELECT '2' stock_id, r.* FROM aa.bb@tab1.dd r
              UNION ALL
              SELECT '3' stock_id, o.* FROM aa.bb@tab2.dd o
              UNION ALL
              SELECT '4' stock_id, b.* FROM aa.bb@tab3.dd b
          )
      )
      SELECT
          trial_id,
          stock_id,
          DELIVERY_DATE_TYPE_ID, 
          to_char(to_date(DELIVERY_SECONDS,'sssss'),'hh24:mi') DELIVERY_HOUR 
      FROM (
          SELECT 
              trial_id,
              stock_id,
              REGEXP_SUBSTR(TRIAL_DELIVERY_DATE_TIME, '[^ ]+', 1, 1) DELIVERY_DATE_TYPE_ID,
              REGEXP_SUBSTR(TRIAL_DELIVERY_DATE_TIME, '[^ ]+', 1, 2) DELIVERY_SECONDS
          FROM (
              SELECT 
                  stock_id, tournumber trial_id,
                      REGEXP_SUBSTR(
                          ABSTRACTSCHEDULE, regEx, 1, 1) t1,
                      REGEXP_SUBSTR(
                          ABSTRACTSCHEDULE, regEx, 1, 2) t2,
                      REGEXP_SUBSTR(
                          ABSTRACTSCHEDULE, regEx, 1, 3) t3,
                      REGEXP_SUBSTR(
                          ABSTRACTSCHEDULE, regEx, 1, 4) t4,
                      REGEXP_SUBSTR(
                          ABSTRACTSCHEDULE, regEx, 1, 5) t5,
                      REGEXP_SUBSTR(
                          ABSTRACTSCHEDULE, regEx, 1, 6) t6,
                      REGEXP_SUBSTR(
                          ABSTRACTSCHEDULE, regEx, 1, 7) t7,
                      REGEXP_SUBSTR(
                          ABSTRACTSCHEDULE, regEx, 1, 8) t8,
                      REGEXP_SUBSTR(
                          ABSTRACTSCHEDULE, regEx, 1, 9) t9,   
                      REGEXP_SUBSTR(
                          ABSTRACTSCHEDULE, regEx, 1, 10) t10    
                      FROM tours
              )
          unpivot (TRIAL_DELIVERY_DATE_TIME for n in (t1, t2, t3, t4, t5, t6, t7, t8, t9, t10))
      )
  </code></pre>