How do I return a boolean value from a query during condition evaluation?

Question

I need something like this:

select (len(someLongTextColumn)=0) as isEmpty;

The above doesn't work,

any alternatives?

Answer 1

If you cast to bit, then most client code can read it as boolean directly (SQL Server doesn't have a boolean type)

SELECT
    CAST(
        CASE
           WHEN len(someLongTextColumn) = 0 THEN 1 ELSE 0
        END AS bit
        ) as isEmpty;

if you have many in one go, use bit variables like this: Imply bit with constant 1 or 0 in SQL Server

Answer 2

Try this.

SELECT (CASE WHEN LEN(SomeLongTextColumn) = 0 THEN 1 ELSE 0 END) AS IsEmtpy

@gbn has good explanation about how to return boolean.

Answer 3

SQL Server:

SELECT CONVERT(BIT, 1) -- true
SELECT CONVERT(BIT, 0) -- false

Answer 4

In MS SQL 2012 and later, you can use IIF as a shorthand:

select IIF(len(someLongTextColumn) = 0, 1, 0) as isEmpty;