What is the best way to convert an int or null to boolean value in an SQL query, such that:
- Any non-null value is TRUE in the results
- Any null value is FALSE in the results
views:
3910answers:
8
A:
SELECT
CASE
WHEN thevalue IS NULL THEN 0
ELSE 1
END AS newVal
FROM .... (rest of select)
I think it goes something like this
Actually, the ISNULL, may need to be WHEN thevalue IS NULL THEN 0
mattlant
2008-10-07 11:09:30
ISNULL() needs a second parameter in T-SQL: ISNULL(value, value-if-null)
Tomalak
2008-10-07 11:16:10
+6
A:
No need to use case... when:
select (column_name is not null) as result from table_name;
Returns 1 for all fields not NULL and 0 for all fields that are NULL, which is as close as you can get to booleans in SQL.
cvk
2008-10-07 11:11:17
isnull() needs a second parameter in T-SQL: ISNULL(value, value-if-null)
Tomalak
2008-10-07 11:12:32
It may work for MySQL, but a lot strange 'convenience' syntax works for MySQL that does not run elsewhere.
Tomalak
2008-10-07 11:27:19
"is null" is a perfectly valid predicate in ANSI SQL. Thi should work everywhere (_should_!)
AJ
2008-10-07 11:29:10
iirc the correct t-sql syntax would be: "column_name not is null" or "is not null" or something like that
cvk
2008-10-07 11:31:07
Not quite. 'ColumnName IS NOT NULL' is the correct syntax. T-SQL has nothing to do with it, this ist standard SQL.
Tomalak
2008-10-07 11:31:20
@Lette: Neither '(column_name IS NULL)' nor '(column_name IS NOT NULL)' do produce any value. They can only be used as a boolean expression, as in CASE WHEN [expression] THEN 'true-value' ELSE 'false-value' END
Tomalak
2008-10-07 12:11:42
+11
A:
To my knowledge (correct me if I'm wrong), there is no concept of literal boolean values in SQL. You can have expressions evaluating to boolean values, but you cannot output them.
This said, you can use CASE WHEN to produce a value you can use in a comparison:
SELECT
CASE WHEN ValueColumn IS NULL THEN 'FALSE' ELSE 'TRUE' END BooleanOutput
FROM
table
Tomalak
2008-10-07 11:11:52
To make it easier to consume the result in code, I prefer returning a bit value where - semantically - a boolean is expected.
Lette
2008-10-07 11:56:06
BIT has it's own pitfalls (at least it T-SQL). It is not a real boolean data type (internally, a bit mask is used to store the values as a union). This has performance and indexing implications. Using CHAR(1) or INT might be more wise.
Tomalak
2008-10-07 12:06:07
Additionally, for SQL Server: "The string values 'TRUE' and 'FALSE' can be converted to bit values: 'TRUE' is converted to 1 and 'FALSE' is converted to 0."
Tomalak
2008-10-07 12:07:53
It is a predicate, but not a value. The question poster asked for a value.
Tomalak
2008-10-07 12:13:51
+1
A:
You may want to do a Convert(BIT, Value) of your result. Because something SQL will return an error that the value is not a boolean.
Patrick Parent
2008-10-07 11:55:00
+1
A:
Assuming you want 0,1 value as a return, and that we are talking about integer I would use the logic specified by Torbjörn and wrap it in the function
create function dbo.isNotNull(@a int)
returns bit
as
begin
return isnull(@a-@a+1,0)
end
so then you can use it whenever you need by simply calling
select dbo.isNotNull(myIntColumn) from myTable
The answer provided by Tomalak is more universal though as it would work with any data type
kristof
2008-10-07 13:20:40
A:
In Oracle, assuming you use 0 for false and 1 for true:-
SELECT DECODE( col, NULL, 0, 1) FROM ...
You can also write this using the CASE syntax, but the above is idiomatic in Oracle. DECODE is a bit like a switch/case; if col is NULL then 0 is returned, else 1.
WW
2008-11-02 06:09:22