CODEWITHSUNDEEP
sql-serverstringnullbit
floormind
floormind

Reputation: 2028

How do i return bit value from a select statement when a column value in the statement is null

In a select query one of the columns is a nullable varchar column. How does one return a 1 or 0 as a bit value if that specific column is NULL?

I have tried doing something like this

select
    isnull(nullif(isnull(JavascriptCode, 0),JavascriptCode), 1) as HasJavaScriptCode
from code

but that seems to return a string value so i cant use in my code.

Upvotes: 7

Views: 13699

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82474

Use CASE:

SELECT CAST(CASE WHEN JavascriptCode IS NULL THEN 0 ELSE 1 END As bit) As HasJavaScriptCode
FROM code

Since 2012 version (which is currently the oldest supported version) SQL Server supports IIF - which can be used instead of simply case expressions to create a more concise code:

SELECT CAST(IIF(JavascriptCode IS NULL, 0,1) As bit) As HasJavaScriptCode
FROM code

Upvotes: 15

Scott
Scott

Reputation: 370

It looks like everyone is agreeing that you should use a case statement. I would point out that you should clearly define if the column might contain an empty string and what that might mean in terms of returning 1 or 0. One solution might look like this:

select
  cast(
    case when JavascriptCode is null or len(JavascriptCode) = 0
      then 0
      else 1
    end
    as bit
  ) as HasJavascriptCode
from code

Just another variation on the many ways you can write this type of statement. This one simply wraps the case statement in a cast to prevent one of the cases from not being a bit. I admit that it isn't as pleasing on the eyes.

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Use CASE expression with CAST:

select
    case when JavascriptCode is null 
         then cast(0 as bit) 
         else cast(1 as bit) end as HasJavaScriptCode
from code

Upvotes: 4

Related Questions