Teradata SQL CASE WHEN报错2620：含非数字Category1值时处理失败

我有一张存储产品故障信息的表，每行对应一条故障记录，包含ID、Category1、Date三列。其中Category1是VARCHAR(1300)类型，值可能是0-999的数字字符串，也可能是其他文本（比如"YES"）。

我需要查询ID、Date列，并基于Category1的值生成条件列X1，执行以下SQL时，当Category1为文本值会触发错误：

CASE 
    WHEN Category1 between 0 and 99 THEN 'CompanyA'
    WHEN Category1 between 100 and 155 THEN 'CompanyB'
    WHEN Category1 between 100 and 155 THEN 'CompanyC'
    WHEN Category1 between 100 and 155 THEN 'CompanyD'
    ELSE 'NULL' 
END AS X1

错误信息：

Executed as single statement. Failed[2620 : HY000] The format or data contains a bad character.

当Category1仅包含0-999的数字字符串时，语句能正常运行。想知道：

1. 为什么即使有ELSE分支，遇到文本值还是会报错？
2. 该如何解决这个问题？

原因分析 #

SQL执行Category1 between 0 and 99这类比较时，会自动把VARCHAR类型的Category1隐式转换为数字类型。如果Category1的值是无法转成数字的文本（比如"YES"），这个转换步骤直接失败，触发错误——CASE语句会先计算所有WHEN条件的表达式，再判断分支，不是遇到不符合的就直接跳去ELSE。所以哪怕有ELSE，只要前面的WHEN条件里存在无法转换的文本，转换错误会先发生，导致整个语句跑崩。

另外注意：你写的CASE语句里后三个WHEN条件完全相同，这会导致只有第一个匹配的分支生效（也就是只会返回'CompanyB'），后面的'CompanyC'和'CompanyD'永远不会被触发，这个逻辑大概率需要调整。

解决方案 #

方案1：先判断是否为有效数字，再做范围比较 #

先通过数据库自带的函数判断Category1是否是合法的数字字符串，再执行后续的范围比较，彻底避免隐式转换失败的问题。以下是几种主流数据库的写法：

MySQL/MariaDB

CASE 
    WHEN Category1 REGEXP '^[0-9]+$' AND CAST(Category1 AS UNSIGNED) BETWEEN 0 AND 99 THEN 'CompanyA'
    WHEN Category1 REGEXP '^[0-9]+$' AND CAST(Category1 AS UNSIGNED) BETWEEN 100 AND 155 THEN 'CompanyB'
    -- 根据实际业务逻辑调整后续分支的范围，比如：
    WHEN Category1 REGEXP '^[0-9]+$' AND CAST(Category1 AS UNSIGNED) BETWEEN 156 AND 200 THEN 'CompanyC'
    WHEN Category1 REGEXP '^[0-9]+$' AND CAST(Category1 AS UNSIGNED) BETWEEN 201 AND 250 THEN 'CompanyD'
    ELSE 'NULL' 
END AS X1

SQL Server

CASE 
    WHEN ISNUMERIC(Category1) = 1 AND CAST(Category1 AS INT) BETWEEN 0 AND 99 THEN 'CompanyA'
    WHEN ISNUMERIC(Category1) = 1 AND CAST(Category1 AS INT) BETWEEN 100 AND 155 THEN 'CompanyB'
    -- 根据实际业务逻辑调整后续分支的范围，比如：
    WHEN ISNUMERIC(Category1) = 1 AND CAST(Category1 AS INT) BETWEEN 156 AND 200 THEN 'CompanyC'
    WHEN ISNUMERIC(Category1) = 1 AND CAST(Category1 AS INT) BETWEEN 201 AND 250 THEN 'CompanyD'
    ELSE 'NULL' 
END AS X1

PostgreSQL

CASE 
    WHEN Category1 ~ '^[0-9]+$' AND CAST(Category1 AS INTEGER) BETWEEN 0 AND 99 THEN 'CompanyA'
    WHEN Category1 ~ '^[0-9]+$' AND CAST(Category1 AS INTEGER) BETWEEN 100 AND 155 THEN 'CompanyB'
    -- 根据实际业务逻辑调整后续分支的范围，比如：
    WHEN Category1 ~ '^[0-9]+$' AND CAST(Category1 AS INTEGER) BETWEEN 156 AND 200 THEN 'CompanyC'
    WHEN Category1 ~ '^[0-9]+$' AND CAST(Category1 AS INTEGER) BETWEEN 201 AND 250 THEN 'CompanyD'
    ELSE 'NULL' 
END AS X1

方案2：字符串范围比较（仅适用于格式统一的数字字符串） #

如果Category1的数字字符串都是固定长度（比如三位：000-999），可以直接用字符串的BETWEEN比较，跳过类型转换：

CASE 
    WHEN Category1 BETWEEN '000' AND '099' THEN 'CompanyA'
    WHEN Category1 BETWEEN '100' AND '155' THEN 'CompanyB'
    -- 根据实际业务逻辑调整后续分支的范围，比如：
    WHEN Category1 BETWEEN '156' AND '200' THEN 'CompanyC'
    WHEN Category1 BETWEEN '201' AND '250' THEN 'CompanyD'
    ELSE 'NULL' 
END AS X1

注意：如果数字字符串长度不一致（比如"5"和"100"），这种方法会出错——因为字符串比较是按字符顺序来的，"5"会被判定为比"100"大，所以仅在能保证数字格式统一的场景下使用。

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