sqlsql-serverstringt-sqlsql-convert

Converting varchar values to decimal while handling NULLs and EMPTY strings as 0

I have a column of varchar datatype populated with a mix of values such as 1.2, 5.33 while also having NULLs and EMPTY strings. I want to convert all the values to decimal while treating NULLs and EMPTY strings as 0.

I can change the NULLs or EMPTY strings using the CONVERT function like below. Like this I replace the NULLs and EMPTY strings with a varhcar 0.

CASE WHEN Column1 = '' OR Column1= NULL THEN '0' ELSE Column1 END AS 'NewColumn1'

However what I want to do is to be able to then convert this data (output of NewColumn1) into decimal but when I place the CASE expression into a CONVERT or a CAST function I will have errors.

I also tried the following. CONVERT(DECIMAL(10,4), ISNULL(Column1, '0')) however it fails since here I am not handling the EMPTY strings.

Any ideas how can I solve this problem.

Solution

  • Simple way:

    SELECT CONVERT(DECIMAL(10, 4), ISNULL(NULLIF(Column1, ''), '0'))

    Your CASE expression doesn't work because you're checking if Column1 = NULL. You should check if it IS NULL.

    CASE WHEN Column1 = '' OR Column1 IS NULL THEN '0' ELSE Column1 END AS 'NewColumn1'