Return true if column has specific value

Question

I have a database where the creator has used decided to use smallint instead of bit for a true/false scenario. False is represented with 0. True is represented with -1. I would like to translate this, preferably in a simpler syntax than CASE. Is there any way I can do something like this:

SELECT (column == -1) 'value_as_bool'

FROM   myTable

Which would (in most languages) translate the column back to a true/false column...

@jarlh I know, but the guy who created the table apparently decided to ignore this. So I'm stuck with `-1` and `0`. And there is quite a few of these columns, so my syntax would be a mess if I have to use `CASE`. — Jakob Busk Sørensen, May 11 '17 at 07:55
@Stephen I changed the example to make it more clear what I would like (hopefully) — Jakob Busk Sørensen, May 11 '17 at 07:58
@jarlh: TSQL has no boolean datatype, so `SELECT (column = -1) AS value_as_bool FROM myTable` fails with an exception. — Thorsten Kettner, May 11 '17 at 07:59
I think it might work with `CAST(column * -1 AS BIT)`, but this has quite a drastical impact on the performance. — Jakob Busk Sørensen, May 11 '17 at 08:05
Tip: It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. — HABO, May 11 '17 at 13:35

score 1 · Answer 1 · answered May 11 '17 at 07:56

1

You can use IIF function. Usage; IIF ( boolean_expression, true_value, false_value )

answered May 11 '17 at 07:56

Burak

186
9

That is simpler than `CASE`, but I have some trouble getting it to work. I have tried like this `IIF(column = -1, 1, 0)` – Jakob Busk Sørensen May 11 '17 at 08:02
1

You should use like this `SELECT IIF(column = 0, 0, 1)` but i forgot to say that IIF comes from SQL 2012. – Burak May 11 '17 at 08:31

score 1 · Answer 2 · answered May 11 '17 at 08:00

1

In a Case, the statement would be:

Case when myTable.column = -1 then 0 else 1 end as [NAME]

You can replace 0 and 1 with FALSE and TRUE, which uses the BOOLEAN/bit type in SQL, if you wish :)

answered May 11 '17 at 08:00

Martin

63
7

General syntax is case when [statement with boolean output] then [value if true] when [statement with boolean output] then [value if true] when ... else [value if all of the above is false] end (possibly with 'as [#NAME#]' afterwards – Martin May 11 '17 at 08:01
How do I control the output? If I use 0 and 1, then will it be output as a `bit` or as some sort of `integer`? – Jakob Busk Sørensen May 11 '17 at 08:08
you can control it by using the CONVERT function, for example use: CONVERT(smallint,1) , which converts 1 to a smallint. documentation of the funktion: https://www.w3schools.com/sqL/func_convert.asp – Martin May 11 '17 at 08:55

score 1 · Accepted Answer · answered May 11 '17 at 08:07

1

I think the simplest solution would be to just cast the int value to a bit. 0 will be False everything else True.

SELECT CAST(column As Bit) 'value_as_bool'
FROM   myTable

answered May 11 '17 at 08:07

Magnus

45,362
8
80
118

I think you are right. I am just have to live with the performance impact. – Jakob Busk Sørensen May 11 '17 at 08:13
If you can change the schema you could always add a persisted computed column to your table and use that instead. You would not get any performance hit. – Magnus May 11 '17 at 08:15
Single quotes are string delimiters. It's double quotes for names in standard SQL and brackets in SQL Server AFAIK. But as `value_as_bool` already is a valid name you need no delimiters at all. – Thorsten Kettner May 11 '17 at 08:25
@ThorstenKettner it works with single quotes, but it also works without. I prefer to avoid double quotes, since the query is to be used in VBA, where double quotes will end the string... – Jakob Busk Sørensen May 11 '17 at 08:33
@Noceo: Better have valid names without any kind of quotes. Using single quotes for names is (according to standard SQL) invalid has its Limits. Just try: `SELECT 'value_as_bool' FROM (SELECT CAST(column As Bit) 'value_as_bool' FROM myTable) t`. – Thorsten Kettner May 11 '17 at 09:05

score 0 · Answer 4 · edited Jun 20 '20 at 09:12

You can declare a variable a return it after set in a IF/ELSE Statement like :

DECLARE @Result Bit

IF column =-1  
    SET @Result = 0
ELSE
    SET @Result = 1
RETURN @Result

Here for more Info about bool values in SQL:

In Management Studio it displays as a false/true value (at least in recent versions).

When accessing the database through ASP.NET it will expose the field as a boolean value.

Hope this help you.

Return true if column has specific value

4 Answers4