Transition through null

Published on June 09, 2018

Transition through null

Many people know and use the built-in function ISNULL (X, Y), which replaces the first argument with the second if it is (first) NULL. The inverse built-in function NULLIF (X, Y) is less used, which returns NULL if the first argument is equal to the second. The combination of these two functions avoids the use of IF-ELSE or CASE-WHEN constructs, which makes the code more compact. If it is interesting to see a couple of examples - welcome under cat.

For example, here's a code that prints 10 random integers in the range from 1 to 37, and the nearest greater or equal number to the displayed number is a multiple of 6.

``````SELECT
Q.Src,
CASE
WHEN Q.Src % 6 = 0 THEN Q.Src
ELSE Q.Src + (6 - Q.Src % 6)
END AS NextTimes6
FROM (
SELECT TOP 10
CONVERT(INT, 1 + 37 * RAND(CHECKSUM(NEWID()))) Src
FROM SYSOBJECTS S
) Q
``````

You can get rid of CASE..WHEN by doing the following trick - converting the result of Q.Src% 6 into NULL if the remainder is 0, knowing that the result of any operation with NULL is NULL, and then restoring NULL back to 0 with an external function ISNULL:

``````SELECT
Q.Src,
Q.Src + ISNULL(6 - NULLIF(Q.Src % 6, 0), 0) AS NextTimes6
FROM (
SELECT TOP 10
CONVERT(INT, 1 + 37 * RAND(CHECKSUM(NEWID()))) Src
FROM SYSOBJECTS S
) Q
``````

Another example is dividing a string into two parts by a space (for example, the name from the string Name <space> Last name. A typical problem here when solving head-on is the fall of the LEFT function when passing the argument -1 to it as a value for the number of characters to be cut in when a space in the source string is not found (CHARINDEX returns 0):

``````SELECT
Q.Src,
CASE
WHEN CHARINDEX(' ', Q.Src) > 0 THEN LEFT(Q.Src, CHARINDEX(' ', Q.Src) - 1)
ELSE Q.Src
END AS NameOnly
FROM (
SELECT N'Петр Иванов' AS Src
UNION ALL
SELECT N'Иван'
) Q
``````

Turns into:

``````SELECT
Q.Src,
LEFT(Q.Src, ISNULL(NULLIF(CHARINDEX(' ', Q.Src), 0) - 1, LEN(Q.Src))) AS NameOnly
FROM (
SELECT N'Петр Иванов' AS Src
UNION ALL
SELECT N'Иван'
) Q
``````