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,
	CASEWHEN Q.Src % 6 = 0THEN Q.Src 
          ELSE Q.Src + (6 - Q.Src % 6) 
        ENDAS NextTimes6
FROM (
	SELECT TOP 10CONVERT(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 10CONVERT(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,
	CASEWHENCHARINDEX(' ', Q.Src) > 0THENLEFT(Q.Src, CHARINDEX(' ', Q.Src) - 1)
	  ELSE Q.Src
    ENDAS 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!