Sum REGEXP_SUBSTR resulting ORA-01722: invalid number error

I’m creating a reconciliation report where I basically just want to know document value of each sent batch.

Batch ID is in its own column and sent data is in one column and it’s comma separated, see randomly generated example below:

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;

Each batch have minimum 2 rows and max can be 2000 rows. In this table I have two columns: Batch ID and Data

I need to sum all the document amounts together so in this case 100 + 200.

Currently I’m trying to get the job done with this query:

SELECT batch_id, SUM(to_number(REGEXP_SUBSTR(data, '[^;]+', 1, 16)))
FROM table
GROUP BY batch_id

But end up with error ORA-01722: invalid number

Thanks in advance and I really appreciate the help I always receive from this site

Answer

Here, Your data is generated like this:

SQL> select REGEXP_SUBSTR('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;', '[^;]+', 1, 16) from dual;

REGEXP
------
100,00

SQL> select REGEXP_SUBSTR('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;', '[^;]+', 1, 16) from dual;

REGEXP
------
250,00

SQL>

Means , is considered as a decimal character in your string.

Let me check with the NLS_NUMERIC_CHARACTERS = '.,' (decimal_character group_separator)

SQL> alter session set NLS_NUMERIC_CHARACTERS = '.,';

Session altered.

SQL> WITH YOUR_DATA(STR) AS
  2  (
  3  SELECT '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;' FROM DUAL
  4  UNION ALL
  5  SELECT '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;' FROM DUAL
  6  )
  7  select SUM(to_number(REGEXP_SUBSTR(STR, '[^;]+', 1, 16))) from YOUR_DATA;
select SUM(to_number(REGEXP_SUBSTR(STR, '[^;]+', 1, 16))) from YOUR_DATA
                     *
ERROR at line 7:
ORA-01722: invalid number


SQL>

Ohh!! I am getting the same error. So You need to use ,. as your NLS_NUMERIC_CHARACTERS parameter.

You can set it in your session and get the correct data as follows:

SQL> alter session set NLS_NUMERIC_CHARACTERS = ',.';

Session altered.
SQL>
SQL> WITH YOUR_DATA(STR) AS
  2  (
  3  SELECT '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;' FROM DUAL
  4  UNION ALL
  5  SELECT '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;' FROM DUAL
  6  )
  7  select SUM(to_number(REGEXP_SUBSTR(STR, '[^;]+', 1, 16))) from YOUR_DATA;

SUM(TO_NUMBER(REGEXP_SUBSTR(STR,'[^;]+',1,16)))
-----------------------------------------------
                                            350

SQL>

You can learn more about NLS_NUMERIC_CHARACTERS from oracle documentation.

For checking the current setting of this NLS parameter, You can use the following query:

SELECT *
  FROM NLS_SESSION_PARAMETERS
 WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';