SQL Server中varchar类型患者身高数据提取数值计算的问题求助

错误原因分析 #

出现Error converting data type varchar to numeric的核心问题：

• 当PATINDEX('%f%', newvalue)返回0（字符串不含'f'）时，LEFT(newvalue, 0)得到空字符串，直接转换DECIMAL会报错。
• 未覆盖纯数字、仅含in的格式场景，转换逻辑缺失。
• 未考虑字符串中存在非数字字符的情况，强制转换会失败。

解决方案代码 #

使用TRY_CONVERT替代CONVERT避免转换报错，同时完善各格式的数值提取逻辑：

SELECT
    newvalue AS original_height,
    -- 提取英尺数值
    ISNULL(TRY_CONVERT(DECIMAL(10,2), 
        CASE
            WHEN newvalue LIKE '%ft%' THEN TRIM(LEFT(newvalue, PATINDEX('%ft%', newvalue) - 1))
            ELSE NULL
        END), 0) AS ft_value,
    -- 提取英寸数值
    ISNULL(TRY_CONVERT(DECIMAL(10,2),
        CASE
            -- 处理ft与in组合的格式
            WHEN newvalue LIKE '%ft%in%' THEN TRIM(SUBSTRING(newvalue, PATINDEX('%ft%', newvalue) + 2, PATINDEX('%in%', newvalue) - PATINDEX('%ft%', newvalue) - 2))
            -- 处理仅含in的格式
            WHEN newvalue LIKE '%in%' THEN TRIM(LEFT(newvalue, PATINDEX('%in%', newvalue) - 1))
            -- 处理纯数字（默认视为英寸，若实际为英尺可调整此处逻辑）
            WHEN newvalue NOT LIKE '%ft%' AND newvalue NOT LIKE '%in%' AND ISNUMERIC(newvalue) = 1 THEN newvalue
            ELSE NULL
        END), 0) AS in_value,
    -- 计算总英寸数
    ISNULL(TRY_CONVERT(DECIMAL(10,2), 
        CASE
            WHEN newvalue LIKE '%ft%' THEN TRIM(LEFT(newvalue, PATINDEX('%ft%', newvalue) - 1))
            ELSE NULL
        END), 0) * 12 +
    ISNULL(TRY_CONVERT(DECIMAL(10,2),
        CASE
            WHEN newvalue LIKE '%ft%in%' THEN TRIM(SUBSTRING(newvalue, PATINDEX('%ft%', newvalue) + 2, PATINDEX('%in%', newvalue) - PATINDEX('%ft%', newvalue) - 2))
            WHEN newvalue LIKE '%in%' THEN TRIM(LEFT(newvalue, PATINDEX('%in%', newvalue) - 1))
            WHEN newvalue NOT LIKE '%ft%' AND newvalue NOT LIKE '%in%' AND ISNUMERIC(newvalue) = 1 THEN newvalue
            ELSE NULL
        END), 0) AS total_inches
FROM your_table_name;

关键逻辑说明 #

• TRY_CONVERT：转换失败时返回NULL，避免抛出转换错误。
• ISNULL(..., 0)：将转换失败的NULL转为0，不影响后续累加计算。
• 纯数字分支：默认按英寸处理，若实际场景中纯数字代表英尺，可将该分支改为NULL，并调整总英寸计算为TRY_CONVERT(DECIMAL(10,2), newvalue)*12。
• 组合格式截取：通过SUBSTRING精准提取ft与in之间的数值，排除多余空格和字符。

测试场景验证 #

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