SQL: Boolean column based on a condition
-
I know I can do this:
SELECT CASE WHEN Something = 'everything' THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS IsSomethingEverythingWhoaMan
but it seems unnecessarily verbose and roundabout to take a condition, generate a new value based on it and cast it back to a boolean. However, just doing
SELECT (Something = 'everything')
doesn't work. Is there a better way, either in generic SQL or particular to MS SQL Server?
-
@hungrier Not that I know of. The closest is probably the IIF function, but that's just shorthand for "case when".
-
You can make it a computed column, to save some finger-work when creating tables. You can make them either computed on-demand, or use the "persisted" keyword to have SQL store them on disk.
Other than that, I believe CASE WHEN is about as good as it gets. You might want to consider handling nulls also.
Now someone's going to call me a hypocrite because I didn't answer the exact question asked. Oh well.
-
@blakeyrat I can't use that in this case since I can't modify the table, but it's a cool feature that I hadn't heard of before.
The condition I need is actually based on whether some column is null, so no worries there. However, funnily enough IIF doesn't seem to work with it, so it looks like case when is my only option.
-
well, you can shove the cast outside the case statement, but that might make it uglier. Other than that...
-
@hungrier said in SQL: Boolean column based on a condition:
@blakeyrat I can't use that in this case since I can't modify the table, but it's a cool feature that I hadn't heard of before.
The condition I need is actually based on whether some column is null, so no worries there. However, funnily enough IIF doesn't seem to work with it, so it looks like case when is my only option.
Does this meet your needs?
SELECT CAST(IIF(Something IS NOT NULL, 1, 0) AS BIT) AS IsSomethingEverythingWhoaMan
Edit: Tested and works as expected in my SQL Server database.
Edit2:
SELECT CAST(IIF(Something = 'everything', 1, 0) AS BIT) AS IsSomethingEverythingWhoaMan
ought to work, too. The reference says:The true_value [second parameter] is returned if the Boolean expression [first parameter] is true, and the false_value [third parameter] is returned if the Boolean expression is false or unknown.
-
@vault_dweller said in SQL: Boolean column based on a condition:
@hungrier Not that I know of. The closest is probably the IIF function, but that's just shorthand for "case when".
TIL
My legacy systems though are still on SQL Server 2008.
-
@djls45 It may be an issue with different versions of SQL Server. When I use
IIF(Something IS NOT NULL, 1, 0)
I getIncorrect syntax near the keyword 'IS'.
-
i vote for the most arcane asinine method possible for such shenanigans
SELECT isnull(bitses,0) as IsSomethingEverythingWhoaMan FROM datatable dt LEFT JOIN (select 'everything' as everything, cast(1 as bit) bitses) as isit ON isit.everything = dt.something
-
@hungrier Ah. That could be. I'm using SQL Server 2012.
Edit: I just noticed the reference says that
IIF
started with SQL Server 2012.
-
@hungrier said in SQL: Boolean column based on a condition:
@djls45 It may be an issue with different versions of SQL Server. When I use
IIF(Something IS NOT NULL, 1, 0)
I getIncorrect syntax near the keyword 'IS'.
See my post.
-
@karla I hadn't tried IIF with any other condition, but it gives me the same thing. So I guess the DB is version $whichever_doesnt_have_iif or older.
-
@darkmatter said in SQL: Boolean column based on a condition:
i vote for the most arcane asinine method possible for such shenanigans
SELECT isnull(everthing,0) as IsSomethingEverythingWhoaMan FROM datatable dt LEFT JOIN (select 'everything' as everything) as isit ON isit.everything = dt.something
For more
select cast(1 as bit) as IsSomethingEverythingWhoaMan where @Something = 'Everything' union select cast(0 as bit) where @Something != 'Everything
-
@karla i thought about the union, but i really wanted to get a left join and isnull (maybe i shoulda coalesced instead, for the fun of it) in there too.
-
@darkmatter said in SQL: Boolean column based on a condition:
@karla i thought about the union, but i really wanted to get a left join and isnull (maybe i shoulda coalesced instead, for the fun of it) in there too.
Yes, yours is definitely more ery than mine.
I'm sure if I put my mind to it, I can do better
-
oops i screwed up my original anyway
i missed using a bit for the success - fixing that nowSELECT coalesce(isit.bitses,orisit.bitses,0) as IsSomethingEverythingWhoaMan FROM datatable dt LEFT JOIN (select 'everything' as everything, cast(1 as bit) bitses) as isit ON isit.everything = dt.something LEFT JOIN (select 'everything' as everything, cast(0 as bit) bitses) as orisit ON orisit.everything != dt.something
and nulls that make neither of those left joins succeed mean coalesce, yay!!
-
-
@hungrier said in SQL: Boolean column based on a condition:
@djls45 It may be an issue with different versions of SQL Server. When I use
IIF(Something IS NOT NULL, 1, 0)
I getIncorrect syntax near the keyword 'IS'.
So just manually explode it to the
CASE
syntax instead:SELECT CAST(CASE WHEN Something IS NOT NULL THEN 1 ELSE 0 END AS BIT) AS IsSomethingEverythingWhoaMan
-
@darkmatter said in SQL: Boolean column based on a condition:
coalesce, yay!!
You need to try and fit
STUFF
in there too
-
Assuming
everything
doesn't appear as a substring in any other valueSELECT CAST(CHARINDEX('everything', Something) AS BIT)
-
@cark That also assumes that's the condition I want to check, rather than a generic example.