title: Sum REGEXP_SUBSTR resulting ORA-01722: invalid number error
body: <p>I'm creating a reconciliation report where I basically just want to know document value of each sent batch.</p>
  
  <p>Batch ID is in its own column and sent data is in one column and it's comma separated, see randomly generated example below:</p>
  
  <pre><code>002;BATCH12345;999;TEST;123940;9999;14052020;1234;123456789;123456;;;;;;FI;;;;;;1234567;;EUR;SPOT;10000000;;100,00;100,00;;Voucher Text;;;;;;;;;;;;;15052020;E;E;E;E;
  002;BATCH12345;999;TEST2;123940;9999;14052020;1234;123456789;123456;;;;;;FI;;;;;;1234567;;EUR;SPOT;10000000;;250,00;250,00;;Voucher Text;;;;;;;;;;;;;15052020;E;E;E;E;
  </code></pre>
  
  <p>Each batch have minimum 2 rows and max can be 2000 rows. In this table I have two columns: Batch ID and Data</p>
  
  <p>I need to sum all the document amounts together so in this case 100 + 200.</p>
  
  <p>Currently I'm trying to get the job done with this query:</p>
  
  <pre><code>SELECT batch_id, SUM(to_number(REGEXP_SUBSTR(data, '[^;]+', 1, 16)))
  FROM table
  GROUP BY batch_id
  </code></pre>
  
  <p>But end up with error ORA-01722: invalid number</p>
  
  <p>Thanks in advance and I really appreciate the help I always receive from this site</p>