is it possible to select EXISTS directly as a bit?

Question

I was wondering if it's possible to do something like this (which doesn't work):

select cast( (exists(select * from theTable where theColumn like 'theValue%') as bit)

Seems like it should be doable, but lots of things that should work in SQL don't ;) I've seen workarounds for this (SELECT 1 where... Exists...) but it seems like I should be able to just cast the result of the exists function as a bit and be done with it.

Answer 1

No, you'll have to use a workaround.

If you must return a conditional bit 0/1 another way is to:

SELECT CAST(
   CASE WHEN EXISTS(SELECT * FROM theTable where theColumn like 'theValue%') THEN 1 
   ELSE 0 
   END 
AS BIT)

Or without the cast:

SELECT
   CASE
       WHEN EXISTS( SELECT 1 FROM theTable WHERE theColumn LIKE 'theValue%' )
            THEN 1 
       ELSE 0 
   END

Answer 2

SELECT CAST(COUNT(*) AS bit) FROM MyTable WHERE theColumn like 'theValue%'

When you cast to bit

• 0 -> 0
• everything else -> 1
• And NULL -> NULL of course, but you can't get NULL with COUNT(*) without a GROUP BY

bit maps directly to boolean in .net datatypes, even if it isn't really...

This looks similar but gives no row (not zero) if no matches, so it's not the same

SELECT TOP 1 CAST(NumberKeyCOlumn AS bit) FROM MyTable WHERE theColumn like 'theValue%'

Answer 3

You can use IIF and CAST

SELECT CAST(IIF(EXISTS(SELECT * FROM theTable 
                       where theColumn like 'theValue%'), 1, 0) AS BIT)

Answer 4

I'm a bit late on the uptake for this; just stumbled across the post. However here's a solution which is more efficient & neat than the selected answer, but should give the same functionality:

declare @t table (name nvarchar(16))
declare @b bit

insert @t select N'Simon Byorg' union select N'Roe Bott'


select @b = isnull((select top 1 1 from @t where name = N'Simon Byorg'),0)
select @b whenTrue

select @b = isnull((select top 1 1 from @t where name = N'Anne Droid'),0)
select @b whenFalse

Answer 5

You can also do the following:

SELECT DISTINCT 1
  FROM theTable
 WHERE theColumn LIKE 'theValue%'

If there are no values starting with 'theValue' this will return null (no records) rather than a bit 0 though

Answer 6

SELECT IIF(EXISTS(SELECT * FROM theTable WHERE theColumn LIKE 'theValue%'), 1, 0)

Answer 7

Another solution is to use ISNULL in tandem with SELECT TOP 1 1:

SELECT ISNULL((SELECT TOP 1 1 FROM theTable where theColumn like 'theValue%'), 0)

Answer 8

No it isn't possible. The bit data type is not a boolean data type. It is an integer data type that can be 0,1, or NULL.

Answer 9

I believe exists can only be used in a where clause, so you'll have to do a workaround (or a subquery with exists as the where clause). I don't know if that counts as a workaround.

What about this:

create table table1 (col1   int null)
go
select 'no items',CONVERT(bit, (select COUNT(*) from table1) )   -- returns 'no items', 0
go
insert into table1 (col1) values (1)
go
select '1 item',CONVERT(bit, (select COUNT(*) from table1) )     --returns '1 item', 1
go
insert into table1 (col1) values (2)
go
select '2 items',CONVERT(bit, (select COUNT(*) from table1) )    --returns '2 items', 1
go
insert into table1 (col1) values (3)
go
drop table table1
go