Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert IS NULL to BIT

This might be a very basic question but I just came over it while writing a query.

Why can't SQL Server convert a check for NULL to BIT? I was thinking about something like this:

DECLARE @someVariable INT = NULL;
-- Do something
SELECT CONVERT(BIT, (@someVariable IS NULL))

The expected outcome would then be either 1 or 0.

like image 958
diiN__________ Avatar asked Apr 27 '16 11:04

diiN__________


People also ask

How do you convert NULL values?

There are two ways to replace NULL with blank values in SQL Server, function ISNULL(), and COALESCE(). Both functions replace the value you provide when the argument is NULL like ISNULL(column, '') will return empty String if the column value is NULL.

Can bit value be NULL?

Bit is one of the odder datatypes, because while it has the smallest domain of values it can represent amongst numeric data (0, 1, NULL), it has one of the largest set of values that can be cast to it.

What is NULL in bit?

The null character (also null terminator) is a control character with the value zero. It is present in many character sets, including those defined by the Baudot and ITA2 codes, ISO/IEC 646 (or ASCII), the C0 control code, the Universal Coded Character Set (or Unicode), and EBCDIC.

Is NULL () in SQL?

SQL Server ISNULL() FunctionThe ISNULL() function returns a specified value if the expression is NULL. If the expression is NOT NULL, this function returns the expression.


2 Answers

Or use IIF (a little more readable than CASE):

CONVERT(BIT, IIF(@x IS NULL, 0, 1))

like image 146
Jovie Avatar answered Oct 06 '22 09:10

Jovie


Use case:

SELECT CONVERT(BIT, (CASE WHEN @someVariable IS NULL THEN 1 ELSE 0 END))
like image 42
Gordon Linoff Avatar answered Oct 06 '22 08:10

Gordon Linoff