61

I made a view to abstract columns of different tables and pre-filter and pre-sort them. There is one column whose content I don't care about but I need to know whether the content is null or not. So my view should pass an alias as "true" in case the value of this specified column isn't null and "false" in case the value is null.

How can I select such a boolean with T-SQL?

slartidan
  • 20,403
  • 15
  • 83
  • 131
Anheledir
  • 4,337
  • 7
  • 32
  • 34

8 Answers8

85

You have to use a CASE statement for this:

SELECT CASE WHEN columnName IS NULL THEN 'false' ELSE 'true' END FROM tableName;
Adam Bellaire
  • 108,003
  • 19
  • 148
  • 163
  • 2
    This results in a varchar(5)-column on the view for me, not a boolean. What am I doing wrong? – Geir Sagberg Jun 25 '13 at 10:46
  • 2
    @uffjohn there's no such thing as "true" or "false" in Sql. Normally it's either 1 or 0, a "bit" flag. If you're using this in .NET code or something, you could cast this value to a boolean there. –  Aug 15 '13 at 19:41
  • 24
    Or do `CAST(CASE WHEN columnName IS NULL THEN 0 ELSE 1 END AS BIT) FROM tableName` – Travis Parks Oct 02 '14 at 21:15
  • This is just flat out wrong. 'true' is not boolean. Labeling the column as bit is also not a bit. – Steve Sether Feb 03 '16 at 20:36
31

Or you can do like this:

    SELECT RealColumn, CAST(0 AS bit) AS FakeBitColumn FROM tblTable
Schnapz
  • 1,208
  • 13
  • 10
20

If you need a output as boolean

CAST(CASE WHEN colName IS NULL THEN 0  ELSE 1   END as BIT) aIsBooked
Mahesh
  • 2,731
  • 2
  • 32
  • 31
9

for the column in the view you can use something like

CASE WHEN ColumnName is not null THEN 'True' ELSE 'False' END

or in a statement 

SELECT 
s.ID,
s.[Name],
CASE WHEN s.AchievedDate is not null THEN 'True' ELSE 'False' END [IsAchieved]
FROM Schools s

or for further processing afterwards I would personally use

SELECT 
s.ID,
s.[Name],
CASE WHEN s.AchievedDate is not null THEN 1 ELSE 0 END [IsAchieved]
FROM Schools s
Arion
  • 31,011
  • 10
  • 70
  • 88
tocsoft
  • 1,709
  • 16
  • 22
6

I had a similar issue where I wanted a view to return a boolean column type based on if an actual column as null or not. I created a user defined function like so:

CREATE FUNCTION IsDatePopulated(@DateColumn as datetime)
RETURNS bit
AS
BEGIN
    DECLARE @ReturnBit bit;

    SELECT @ReturnBit = 
        CASE WHEN @DateColumn IS NULL 
            THEN 0 
            ELSE 1 
        END

    RETURN @ReturnBit
END

Then the view that I created returns a bit column, instead of an integer.

CREATE VIEW testView
AS
    SELECT dbo.IsDatePopulated(DateDeleted) as [IsDeleted] 
    FROM Company
lcrepas
  • 748
  • 1
  • 6
  • 10
5

You asked for boolean, which we call bit in t-sql.

Other answers have either given you a varchar 'true' and 'false' or 1 and 0. 'true' and 'false' are obviously varchar, not boolean. I believe 1 and 0 would be cast as an integer, but it's certainly not a bit. This may seem nit-picky, but types matter quite often.

To get an actual bit value, you need to cast your output explicitly as a bit like:

select case when tableName.columnName IS NULL then cast(0 as bit) else cast(1
as bit) END as ColumnLabel from tableName
Mus
  • 7,290
  • 24
  • 86
  • 130
Steve Sether
  • 140
  • 2
  • 9
1

I think this is slightly simpler then the other solutions:

SELECT Cast(ISNULL([column name], 0) AS BIT) AS IsWhatever

todji
  • 141
  • 7
1

Since SQL server 2012 you can use IIF

IIF(columnName IS NULL, 'false', 'true')
Mirek Michalak
  • 1,075
  • 7
  • 11