CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Chris B
Chris B

Reputation: 5441

Looking for an alternative to messy SQL ISNULL/NULLIF

I have a SQL query running on SQL Server 2012 that needs to compare a bit value and return a string if that value is 1 and an empty string if it is zero.

Originally I had it as a CASE statement like this:

CASE WHEN myBit = 0 THEN 
 -- do other comparisons etc to build up the return string.
 +'myString' 
ELSE 
-- do other comparisons etc to build up the return string.
'' END

The problem is that all of the code in 'do other' section is the same. All I want to do is append a string to the returned value if the bit is zero and nothing if it is 1.

So I refactored it to only have the common code once and then append to the string at the end like this:

-- do other comparisons etc to build up the return string. +
ISNULL(NULLIF(Cast(ISNULL(CAST(NULLIF(myBit, 0) AS NVARCHAR), 'myString') AS varchar),'0'),'')

However the above seems very messy not least because of the CAST statements required.

I'm looking for a clean and neat way of doing this but have run out of ideas - anyone have a better way of achieving this? Thanks.

Upvotes: 3

Views: 2194

Answers (4)

Kaf
Kaf

Reputation: 33809

Also you can use a simple case here as;

Select 'Your other string ' +  
       Case mybit When 0 then 'mystring' else '' End As Results

--Results will be like
mybit    Results
0        'Your other string mystring'
1        'Your other string '

OR if you want nothing (null) to return if mybit <> 0 then use a simple case without else part as;

Select 'Your other string ' +  Case mybit When 0 then 'mystring' End As Results

--Results will be like
mybit    Results
0        'Your other string mystring'
1        null

SQL-SERVER-DEMO for both cases

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453028

You can also use IIF and CONCAT as you are on SQL Server 2012. 

SELECT CONCAT('Start',
              IIF(@mybit=0,'myString',''),
              'End')

IIF is a bit more concise than CASE. CONCAT might be beneficial in that it casts non string types to strings automatically and concatenating NULL is treated the same as concatenating an empty string.

Upvotes: 3

Dan Bracuk
Dan Bracuk

Reputation: 20794

Can you put the common code into a subquery? It would resemble this:

select case when myBit = 0 then value else value + 'what you append' end returnvalue
from
(subquery with common code) abc

Or maybe a function

select case when myBit = 0 then yourfunction() 
else yourfunction + 'what you append' end returnvalue

Upvotes: 0

Bill
Bill

Reputation: 4585

Just add your CASE statement inline. Don't forget to return an empty string when mybit=1, or the whole thing will return NULL.

Select 
-- do other comparisons etc to build up the return string.
 + Case When @mybit=0 Then mystring else '' End

Upvotes: 4

Related Questions