SQL使用正则表达式时出现Numeric data not valid错误排查

问题根源 #

1. TRIM破坏正则匹配准确性：你对WRKTITLE执行TRIM后再做正则匹配，原格式中结尾的固定空格是合法格式的一部分，TRIM会删除首尾空格，导致原本不符合格式的记录（比如首尾有多余空格、中间格式错位但TRIM后符合正则的记录）被误匹配，这些记录的SUBSTR(WRKTITLE,26,8)可能不是纯数字，引发转换错误。

2. 数据库条件执行顺序不固定：WHERE子句的条件执行顺序并非严格按编写顺序，数据库可能先执行INT(SUBSTR(WRKTITLE , 26, 8) ) > 0，再过滤正则匹配的记录，导致非数字内容提前被INT函数转换，触发错误。

3. 固定位置提取的脆弱性：依赖固定位置SUBSTR(26,8)提取日期数字，一旦字段内容的空格数量、格式细节有偏差（比如某部分多/少了空格），提取的内容就不是纯数字，引发转换失败。

解决方法 #

方法1：修正正则匹配，去掉TRIM #

直接对原始WRKTITLE做正则匹配，保留原格式的空格要求：

SELECT WORKKEY , WRKTITLE, INT(SUBSTR(WRKTITLE , 26, 8) ) 
FROM IACDTAA.IACWTL
WHERE 
    REGEXP_LIKE(WRKTITLE, '^[0-9]{1,4} ARTX LU 079 PUBLDTE=[0-9]{8}       A$')
    AND WORKKEY IN (SELECT WORKKEY FROM IACDTAA.IACNTC WHERE NTFCTLKEY = 195586)
    AND INT(SUBSTR(WRKTITLE , 26, 8) ) > 0 ;

方法2：用子查询提前过滤合法记录 #

通过子查询先筛选出符合正则的记录，再在外层做数值转换和比较，避免数据库提前执行转换操作：

SELECT WORKKEY, WRKTITLE, INT(publdte_num)
FROM (
    SELECT WORKKEY, WRKTITLE, SUBSTR(WRKTITLE, 26, 8) AS publdte_num
    FROM IACDTAA.IACWTL
    WHERE 
        REGEXP_LIKE(WRKTITLE, '^[0-9]{1,4} ARTX LU 079 PUBLDTE=[0-9]{8}       A$')
        AND WORKKEY IN (SELECT WORKKEY FROM IACDTAA.IACNTC WHERE NTFCTLKEY = 195586)
) t
WHERE INT(publdte_num) > 0;

方法3：用正则提取数字，避免固定位置依赖 #

改用REGEXP_SUBSTR直接提取PUBLDTE=后的8位数字，无需依赖固定位置，更健壮：

SELECT WORKKEY , WRKTITLE, INT(REGEXP_SUBSTR(WRKTITLE, 'PUBLDTE=([0-9]{8})', 1, 1, 'i', 1)) AS publdte
FROM IACDTAA.IACWTL
WHERE 
    REGEXP_LIKE(WRKTITLE, '^[0-9]{1,4} ARTX LU 079 PUBLDTE=[0-9]{8}       A$')
    AND WORKKEY IN (SELECT WORKKEY FROM IACDTAA.IACNTC WHERE NTFCTLKEY = 195586)
    AND INT(REGEXP_SUBSTR(WRKTITLE, 'PUBLDTE=([0-9]{8})', 1, 1, 'i', 1)) > 0 ;

内容的提问来源于stack exchange，提问作者Theju112