2

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.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
user2307236
  • 665
  • 4
  • 19
  • 44

2 Answers2

6

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'
Marko Juvančič
  • 5,792
  • 1
  • 25
  • 41
  • Thank you Marko. This first solution is working and also very clean. – user2307236 Apr 04 '17 at 07:47
  • 1
    @user2307236 if this answer solved your issue you have to [accept it](http://www.stackoverflow.com/tour), it is not sufficient to say thanks. Also When having many well answered question without accepting good answers. Other user will not pay attention for your questions. good luck – Yahfoufi Apr 14 '17 at 13:48
2

Try this,

SELECT CAST(ISNULL(NULLIF(Column1, ''),0) AS DECIMAL(12,2)) FROM table
Kushan
  • 10,657
  • 4
  • 37
  • 41