时间戳

首页 > 解决方案 > 求和 REGEXP_SUBSTR 结果 ORA-01722: 无效数字错误

sql - 求和 REGEXP_SUBSTR 结果 ORA-01722: 无效数字错误

问题描述

我正在创建一个对账报告，我基本上只想知道每个发送批次的文档价值。

批次 ID 在其自己的列中，发送的数据在一列中，并且以逗号分隔，请参见下面随机生成的示例：

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;

每批最少有 2 行，最多可以是 2000 行。在此表中，我有两列：批次 ID 和数据

我需要将所有文件金额加在一起，因此在本例中为 100 + 200。

目前我正在尝试使用此查询完成工作：

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

但最终出现错误 ORA-01722: invalid number

在此先感谢，我非常感谢我一直从这个网站获得的帮助

标签： sqloracleoracle11g

解决方案

在这里，您的数据是这样生成的：

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>

均值,被视为字符串中的十进制字符。

让我检查一下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>

哦！！我遇到了同样的错误。所以你需要使用,.作为你的NLS_NUMERIC_CHARACTERS参数。

您可以在会话中设置它并获取正确的数据，如下所示：

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>

NLS_NUMERIC_CHARACTERS您可以从oracle 文档中了解更多信息。

要检查此NLS参数的当前设置，您可以使用以下查询：

SELECT *
  FROM NLS_SESSION_PARAMETERS
 WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';

推荐阅读