title: Extract Value From Text And Convert Row To Column
body: <p><code>tbl1</code> Is </p>
  
  <pre>
  ID | TXT                                                                                                   
  -- | -----------------------------------------------------------------------------
   1 | Material discharged in hopper: Dest: FURNACE; ; 312 - CALCINE DOLOMIT: 1505kg;                        
   2 | Dest: FURNACE; AUTO; 312 - CALCINE DOLOMIT: 1505kg;                                                   
   3 | Material discharged in hopper: Dest: FURNACE; ; 401 - LIME-LUMP: 993kg; 312 - CALCINE DOLOMIT: 521kg; 
   4 | Dest: FURNACE; AUTO; 401 - LIME-LUMP: 993kg; 312 - CALCINE DOLOMIT: 521kg;                            
   5 | Material discharged in hopper: Dest: FURNACE; ; 400 - LIME-LUMP: 1007kg;                              
   6 | Dest: FURNACE; AUTO; 400 - LIME-LUMP: 1007kg;                                                         
  </pre>
  
  <p>I want to extract numbers for <code>LIME-LUMP</code> and <code>CALCINE DOLOMIT</code> and show in separate columns like this:</p>
  
  <pre>
  ID | CALCINE DOLOMIT| LIME-LUMP                                                                                                   |
  -- | -----------------------------------------------------------------------------
   1 | 1505           |0                   
   2 | 1505           |0                                               
   3 | 993            |521 
   4 | 993            |521                      
   5 | 0              |1007                             
   6 | 0              |1007                                                         
  </pre>
  
  <p>I wrote this query:</p>
  
  <pre><code>select id,txt,
          REGEXP_SUBSTR(txt, '\d+', 1, 2) AS CALCINE_DOLOMIT,
          REGEXP_SUBSTR(txt, '\d+', 1, 4) AS LIME_LUMP 
  from tbl1  
  </code></pre>
  
  <p>but this query returns the <code>Lime Lump</code> value for <code>Calcine dolomit</code> sometimes...</p>
  
  <p>After extracting the values I want to move row 2,4,6 (TXT column has <code>Dest:</code> string) To <code>DEST id</code> column like this:</p>
  
  <pre>
  ID | CALCINE DOLOMIT| LIME-LUMP |DEST id
  -- | --------------------------------------
   1 | 1505           |0          |2                                            
   3 | 993            |521        |4   
   5 | 0              |1007       |6                                                             
  </pre>
  
  <p>Please help me...</p>
  
  <p><em>db&lt;>fiddle <a href="https://dbfiddle.uk/?rdbms=oracle_11.2&amp;fiddle=af9dcbf92c68963df5408e17e6348274" rel="nofollow noreferrer">here</a></em></p>