如何在SQL查询中将sql列空值转换为0，varchar转换为十进制

Question

我需要将varchar数据类型中的SQL列转换为decimal，并将空值转换为0。这是我将varchar转换为decimal的代码

SELECT 
    CAST(debit as DECIMAL(9,2)),
    CAST(credit as DECIMAL(9,2)),
    sum_accname,
    sum_date, 
    sum_description
FROM 
    sum_balance

我需要将借方和信用列空值转换为零。如何将空值转换为此代码？

Answer 1

SELECT 
    COALESCE(CAST(debit as DECIMAL(9,2)),0) as debit,
    COALESCE(CAST(credit as DECIMAL(9,2)),0) as credit,
    sum_accname,
    sum_date, 
    sum_description
FROM 
    sum_balance

Answer 2

使用COALESCE函数获取非空值。

试试这个：

SELECT 
    CAST(COALESCE(debit, '0') AS DECIMAL(9,2)),
    CAST(COALESCE(credit, '0') AS DECIMAL(9,2)),
    sum_accname,
    sum_date, 
    sum_description
FROM 
    sum_balance;

Answer 3

尝尝这个

SELECT 
    CAST(isnull(debit ,0) as DECIMAL(9,2)),
    CAST(isnull(credit,0) as DECIMAL(9,2)),
    sum_accname,
    sum_date, 
    sum_description
FROM 
    sum_balance