Replace NULL with Blank

SQL Server Administration
,
1

Hi Experts,
I need your guidance here.

CASE WHEN CLASS = 'SU' and COUNT > 0 THEN (SELLP - BUYC)/SELLP ELSE ' ' END AS [Disc]
When using the above code I am getting an error as "Error converting data type varchar to numeric"

it works when I update the query
CASE WHEN CLASS = 'SU' and COUNT > 0 THEN (SELLP - BUYC)/SELLP ELSE NULL END AS [Disc]

I want to replace NULL with ' '. Please guide.

Thank You,
Yoga

2

hope this helps

you can use

ISNULL( column , '')

:+1: :wink:

3

A column, and therefore a case expression, can only have one datatype. ie A column cannot be both a number and a string.

Formatting is really a job for the front end so I would be inclined to leave the ELSE as NULL.

If you really want the blank you will either have to cast to a string or sql_variant.

CASE WHEN CLASS = 'SU' and COUNT > 0 THEN CAST((SELLP - BUYC)/SELLP AS varchar(20)) ELSE '' END

CASE WHEN CLASS = 'SU' and COUNT > 0 THEN CAST((SELLP - BUYC)/SELLP AS sql_variant) ELSE CAST('' AS sql_variant) END

ps I am surprised you got an error as I suspect the '' should just be cast to 0 (zero).

4

For integer values, SQL will allow '' and implicitly convert it to 0. But for decimal values, SQL doesn't allow ''.