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
    

    Enjoy your SQL programming!