What is the meaning behind this SQL Statement? ISNULL(Status,0) & 128 = 0?
Some of the stored procedures in my application, using
WHERE condition like this:
ISNULL(Status,0) & 128 = 0
What exactly does it mean or how it compares? I understand
isnull condition, but single
& operator what purpose, want to know. Can any one suggest me?
SQL Server ISNULL Function, The following shows the syntax of the ISNULL() function: SELECT id, ISNULL( min_age,0) min_age, ISNULL(max_age,99) max_age FROM divisions;. SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL
ISNULL(Status, 0) & 128 = 0 checks if the
8th bit of the
Status column is equal to
1. ISNULL() replaces NULL with the specified replacement value and & (bitwise AND) performs a bitwise logical AND operation between two integer values.
One explanation for this check is that sometimes is more appropriate to store multiple bit values in one column, instead of creating multiple bit columns for each value. This approach is based on additional calculation using the
POWER function. After that, the
& (BITWISE AND) operator is needed to get the value for each stored switch.
The next example, based on the statement in the question, shows how to store the values for 7 switches, in one column, and then check the status for a given switch.
-- Calculation DECLARE @switch int SELECT @switch = ( POWER(2 * 0, 1) + -- switch 1 is off POWER(2 * 0, 2) + -- switch 2 is off POWER(2 * 0, 3) + -- switch 3 is off POWER(2 * 0, 4) + -- switch 4 is off POWER(2 * 0, 5) + -- switch 5 is off POWER(2 * 0, 6) + -- switch 6 is off POWER(2 * 1, 7) -- switch 7 is on ) -- Check for switch 7. 128 is equal to POWER(2, 7) IF ISNULL(@switch, 0) & 128 = 0 PRINT 'OFF' ELSE PRINT 'ON' -- All checks SELECT CASE WHEN ISNULL(@switch, 0) & POWER(2, 1) /* or 2 */ = 0 THEN 'Switch1 is OFF' ELSE 'Switch1 IS ON' END, CASE WHEN ISNULL(@switch, 0) & POWER(2, 2) /* or 4 */ = 0 THEN 'Switch2 is OFF' ELSE 'Switch2 IS ON' END, CASE WHEN ISNULL(@switch, 0) & POWER(2, 3) /* or 8 */ = 0 THEN 'Switch3 is OFF' ELSE 'Switch3 IS ON' END, CASE WHEN ISNULL(@switch, 0) & POWER(2, 4) /* or 16 */ = 0 THEN 'Switch4 is OFF' ELSE 'Switch4 IS ON' END, CASE WHEN ISNULL(@switch, 0) & POWER(2, 5) /* or 32 */ = 0 THEN 'Switch5 is OFF' ELSE 'Switch5 IS ON' END, CASE WHEN ISNULL(@switch, 0) & POWER(2, 6) /* or 64 */ = 0 THEN 'Switch6 is OFF' ELSE 'Switch6 IS ON' END, CASE WHEN ISNULL(@switch, 0) & POWER(2, 7) /* or 128 */ = 0 THEN 'Switch7 is OFF' ELSE 'Switch7 IS ON' END
SQL Null Functions, In the example above, if any of the "UnitsOnOrder" values are NULL, the result will be returns TRUE (-1) if the expression is a null value, otherwise FALSE (0):. SQL Server The SQL Server ISNULL() function lets you return an alternative value when an expression is NULL: SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
& is & (Bitwise AND) (Transact-SQL) and is defined as:
Performs a bitwise logical AND operation between two integer values.
The & bitwise operator performs a bitwise logical AND between the two expressions, taking each corresponding bit for both expressions. The bits in the result are set to 1 if and only if both bits (for the current bit being resolved) in the input expressions have a value of 1; otherwise, the bit in the result is set to 0.
If the left and right expressions have different integer data types (for example, the left expression is smallint and the right expression is int), the argument of the smaller data type is converted to the larger data type. In this case, the smallintexpression is converted to an int.
In this case, when the value of
Status have a
1 for the bit value 128 (
10000000) then the condition would be false. So numbers such as
255 would be not meet the
WHERE clause requirement, or numbers like
0110011100), as the bit representing 128 has the value
On a side note, I would personally write the above as:
WHERE (Status & 128 = 0 OR Status IS NULL)
SQL ISNULL function, We define the following parameters while designing a table in SQL Server. Data types for The syntax for the SQL ISNULL function is as follow. SQL Server converts the data type of replacement to data type of expression. I need to write a query that will gimme the resultsets based on a data comparison with some values, Those values that are null in the result set needs to be replaced with an empty string which can be obtained using ISNULL function. My question here is, how to incorporate this in a SELECT statement that has CASE expression.
ISNULL (Transact-SQL), Full-text search � Joins � Scripting � Subqueries � Synonyms � User-defined functions To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation. Is the expression to be checked for NULL. check_expression can be of any type. Volume Discount, 0.10, 25, 0. The following condition: ISNULL(Status,0) & 128 = 0 Evaluates whethere the 8th bit of Status (=128) is not set. Using the & operator performs a bitwise AND to evaluate whether two values share the same bits.
SQL Null Functions, SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions. Look at the NULL values. We have the following SELECT statement: In the example above , if any of the "UnitsOnOrder" values are NULL, the result is NULL. SELECT ProductName,UnitPrice*(UnitsInStock+IIF(ISNULL(UnitsOnOrder),0, UnitsOnOrder)) Name Null Type FIRST_NAME CHAR(25) LAST_NAME CHAR(25) SALARY NUMBER(6) . Here, above on using DESC or either DESCRIBE we are able to see the structure of a table but not on the console tab, the structure of table is shown in the describe tab of the Database System Software.
SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL