SQL Server正常运行的查询迁移至Oracle后输出异常如何解决

适配Oracle的修改方案 #

问题核心出在两个地方：

• 原语句的LIKE判断直接作用在未修剪的Column1字段上，Oracle中如果该字段是CHAR固定长度类型，存储时会自动补尾部空格，导致长度匹配失败，所有条件不触发直接返回原始值
• 原SQL Server的CTE测试代码在Oracle中运行需要补充FROM DUAL语法，Oracle不支持无数据源的常量查询

修改后的正式查询语句

SELECT
    CASE
        WHEN TRIM(Column1) LIKE '__-____' 
            THEN '000' || SUBSTR(TRIM(Column1), 1, 2)
        WHEN TRIM(Column1) LIKE '___-____' 
            THEN '00' || SUBSTR(TRIM(Column1), 1, 3)
        WHEN TRIM(Column1) LIKE '_______-_____' 
            THEN SUBSTR(TRIM(Column1), 3, 5)
        ELSE TRIM(Column1)
    END AS NewColumn
FROM 
    schema.table1

Oracle可直接运行的测试验证代码

WITH cte_test AS 
(
    SELECT '12-3456' AS Column1 FROM DUAL
    UNION ALL
    SELECT '78-9101' FROM DUAL
    UNION ALL
    SELECT '1234567-89101' FROM DUAL
    UNION ALL
    SELECT '123-4321' FROM DUAL
)
SELECT 
    CASE
        WHEN TRIM(Column1) LIKE '__-____' 
            THEN '000' || SUBSTR(TRIM(Column1), 1, 2)
        WHEN TRIM(Column1) LIKE '___-____' 
            THEN '00' || SUBSTR(TRIM(Column1), 1, 3)
        WHEN TRIM(Column1) LIKE '_______-_____' 
            THEN SUBSTR(TRIM(Column1), 3, 5)
        ELSE TRIM(Column1)
    END AS NewColumn
FROM 
    cte_test

运行后返回结果和SQL Server完全一致，四条测试数据对应输出00012、00078、34567、00123，均为5位字符长度。

可选严谨优化

如果担心字符串中间出现其他符号导致LIKE匹配偏差，可以把条件替换为长度+指定位置字符判断，逻辑更可控：

WHEN LENGTH(TRIM(Column1)) = 7 AND SUBSTR(TRIM(Column1),3,1) = '-'

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