CODEWITHSUNDEEP
sql-servert-sql
Ben Foster
Ben Foster

Reputation: 34830

SQL SELECT - Return boolean based on condition

Essentially I'm trying to do this

select  u.Hostname, u.IsCustom, (u.Status = 5) as IsActive
from    SiteUrlMappings u

Where 5 is an int representing an "Active" url.

Of course this doesn't work, and my sql is rusty like an old screwdriver.

Upvotes: 37

Views: 62409

Answers (5)

gbn
gbn

Reputation: 432742

You don't need a CASE expression
Just leverage how bit works: all non-zero values give 1 when cast to bit

SELECT
    u.Hostname,
    u.IsCustom,
    ~ CAST((u.Status = 5) AS bit) AS IsActive
from    SiteUrlMappings u

Upvotes: 40

Barry Kaye
Barry Kaye

Reputation: 7759

Try this:

SELECT
    u.Hostname,
    u.IsCustom,
    CASE
WHEN u.Status = 5 THEN
    1
ELSE
    0
END AS IsActive
from    SiteUrlMappings u

Upvotes: 7

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52157

You can use CASE ... WHEN syntax instead.

Upvotes: 0

Factor Mystic
Factor Mystic

Reputation: 26800

You need a case statement, like this:

select  u.Hostname,
        u.IsCustom,
        convert(bit, case when u.Status = 5 then 1 else 0 end) as IsActive

from    SiteUrlMappings u

bit is as close to true boolean as you can get in SQL Server

Upvotes: 9

Martin Smith
Martin Smith

Reputation: 454020

SQL Server doesn't have a boolean datatype. The closest is bit

SELECT u.Hostname,
       u.IsCustom,
       CAST(CASE
              WHEN u.Status = 5 THEN 1
              ELSE 0
            END AS BIT) AS IsActive
FROM   SiteUrlMappings u

Upvotes: 34

Related Questions