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 statement 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.
Simple way:
SELECT CONVERT(DECIMAL(10, 4), ISNULL(NULLIF(Column1, ''), '0'))
Your CASE
statement doesn't work because you're cheking if Column1 = NULL
. You sholud check if it IS NULL
.
CASE WHEN Column1 = '' OR Column1 IS NULL THEN '0' ELSE Column1 END AS 'NewColumn1'