Oracle提取单元格数据遇ORA-00911错误，求排查及数据规整方案

问题背景 #

需要从内容可变的列中提取C26cupo对应的值实现数据同质化，但执行SQL时触发ORA-00911: invalid character错误，同时需解决数据提取规整问题。

原始数据 #

注：最后一条记录除C26cupo:外，还包含C26edadretiroforzoso:和C26limimite edad:，且冒号后有空格。

期望结果 #

原始报错SQL #

SELECT 
  K.C93USUARIO, 
  K.C93FECHA_APL, 
  CONCAT('C26cupo:', TO_NUMBER(SUBSTR(K.C93VALANTES, INSTR(K.C93VALANTES, 'C26cupo:') + LENGTH('C26cupo:')))) AS C93VALANTES
FROM t93_log K
WHERE K.C93USUARIO IN ('vkmateus918')
  AND K.C93FECHA_APL >= TO_DATE('2022/10/01', 'yyyy/mm/dd')
  AND K.C93FECHA_APL <= TO_DATE('2022/10/01', 'yyyy/mm/dd')
  AND K.C93IDPROCESO IN ('pagaduriabean') 
  AND K.C93TRANSACCION IN ('UPDATE')
  AND K.C93VALANTES NOT LIKE '%CONSOLIDADORA%'
  AND K.C93VALANTES NOT LIKE '%C26OPCIONCAMPO2 :%'
  AND K.C93CAMPO LIKE '%C26CUPO%'
  AND INSTR(K.C93VALANTES, 'C26cupo:') > 0

问题排查与解决 #

1. ORA-00911错误原因 #

原始SQL中的>=和<=是HTML转义字符，Oracle无法识别，需替换为实际的>=和<=符号。

2. 数据提取逻辑缺陷 #

原始SUBSTR会截取C26cupo:之后的所有内容，对包含其他字段的记录（如BMARIN行），会带入无关内容导致TO_NUMBER转换失败，需调整截取逻辑，只提取C26cupo:之后的数字部分。

修正后的SQL #

SELECT 
  K.C93USUARIO, 
  K.C93FECHA_APL, 
  'C26cupo : ' || TO_NUMBER(
    TRIM(
      SUBSTR(
        K.C93VALANTES, 
        INSTR(K.C93VALANTES, 'C26cupo : ') + LENGTH('C26cupo : '),
        INSTR(K.C93VALANTES, ' ', INSTR(K.C93VALANTES, 'C26cupo : ') + LENGTH('C26cupo : ')) 
        - (INSTR(K.C93VALANTES, 'C26cupo : ') + LENGTH('C26cupo : '))
      )
    )
  ) AS C93VALANTES
FROM t93_log K
WHERE K.C93USUARIO IN ('vkmateus918')
  AND K.C93FECHA_APL >= TO_DATE('2022/10/01', 'yyyy/mm/dd')
  AND K.C93FECHA_APL <= TO_DATE('2022/10/01', 'yyyy/mm/dd')
  AND K.C93IDPROCESO IN ('pagaduriabean') 
  AND K.C93TRANSACCION IN ('UPDATE')
  AND K.C93VALANTES NOT LIKE '%CONSOLIDADORA%'
  AND K.C93VALANTES NOT LIKE '%C26OPCIONCAMPO2 :%'
  AND K.C93CAMPO LIKE '%C26CUPO%'
  AND INSTR(K.C93VALANTES, 'C26cupo : ') > 0

逻辑说明 #

• 替换HTML转义字符为实际比较符号，解决ORA-00911错误；
• 通过INSTR定位C26cupo : 之后的第一个空格，作为截取结束位置，确保只提取数字；
• 用TRIM去除数字前后空格，避免转换报错；
• 使用Oracle原生字符串连接符||替代CONCAT（CONCAT仅支持两个参数，||更灵活）。

补充优化（可选） #

若字段格式存在空格数量不固定的情况，用正则表达式提取兼容性更强：

SELECT 
  K.C93USUARIO, 
  K.C93FECHA_APL, 
  'C26cupo : ' || TO_NUMBER(REGEXP_SUBSTR(K.C93VALANTES, 'C26cupo\s*:\s*(\d+)', 1, 1, NULL, 1)) AS C93VALANTES
FROM t93_log K
WHERE K.C93USUARIO IN ('vkmateus918')
  AND K.C93FECHA_APL >= TO_DATE('2022/10/01', 'yyyy/mm/dd')
  AND K.C93FECHA_APL <= TO_DATE('2022/10/01', 'yyyy/mm/dd')
  AND K.C93IDPROCESO IN ('pagaduriabean') 
  AND K.C93TRANSACCION IN ('UPDATE')
  AND K.C93VALANTES NOT LIKE '%CONSOLIDADORA%'
  AND K.C93VALANTES NOT LIKE '%C26OPCIONCAMPO2 :%'
  AND K.C93CAMPO LIKE '%C26CUPO%'
  AND REGEXP_LIKE(K.C93VALANTES, 'C26cupo\s*:\s*\d+')

• 正则表达式C26cupo\s*:\s*(\d+)可匹配任意数量空格的格式，并精准捕获数字部分。

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