CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
user680865
user680865

Reputation: 299

Getting zero instead of Blank value

Why below code gives me 0 or zero instead of blank value or blank row in column ValueA.

How can I change it to blank value?

select ValueA = case when ValueB = 'No' then ValueA
                     when ValueB = 'Yes' then ''
                     else ''
                     end
From Table

or

if I use ValueB = 'Yes' then CAST(NULL AS varchar(25)) gives me Null and I want blank where it is Null, but when I try to convert to blank I get zero. Why?

How can I get blank?

Upvotes: 2

Views: 3077

Answers (4)

HABO
HABO

Reputation: 15852

A given case expression will always return a value of a single data type. If the when and else clauses result in different data types then the rules of data type precedence will be applied. The result of the case will always be the highest precedence data type of all of the when and else clause values.

In this instance is appears that ValueA is likely a numeric data type, hence the return value is 0. Since the numeric data types have a higher data type precedence than strings the empty strings ('') are converted to numeric values resulting in 0.

The solution is to explicity cast (or convert or, in newer versions of SQL Server, format) the numeric value to a string so that all branches of the case return a string data type.

Using a searched case:

select
    case when ValueB = 'No' then Cast( ValueA as VarChar(10) )
         when ValueB = 'Yes' then '' 
         else '' 
         end as ValueA
    from Table;

Or a simple case:

select
    case ValueB
         when 'No' then Cast( ValueA as VarChar(10) )
         when 'Yes' then '' 
         else '' 
         end as ValueA
    from Table;

The 'Yes' branch is effectively redundant in both examples and can be eliminated since the else clause will return the same value.

Alternatively, more modern versions of SQL Server provide iif:

select iif( ValueB = 'No', Cast( ValueA as VarChar(10) ), '' ) as ValueA
    from Table;

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

ValueA must be a numeric data type. Also, your WHEN 'Yes' and ELSE are redundant. Try:

SELECT ValueA = CASE ValueB 
       WHEN 'No'  THEN CONVERT(VARCHAR(25), ValueA)
       ELSE '' END
FROM dbo.[Table];

Upvotes: 2

Derek
Derek

Reputation: 23318

Because valueA is a number, and it can't contain a "blank row". It's either going to be 0 or NULL. If you want blanks, you need to use varchar or an equivalent character field.

Upvotes: 1

mservidio
mservidio

Reputation: 13057

Sounds like ValueA is equal to 0.

Upvotes: 1

Related Questions