How do I propagate NULL values in a custom expression?

Question

I currently have a query on the following form:

SELECT
    CASE
        WHEN <column> IS NOT NULL THEN <expression>
        ELSE NULL
    AS <new_column>,
    CASE
        WHEN <other_column> IS NOT NULL THEN <other_expression>
        ELSE NULL
    AS <new_other_column>,
    -- etc...

I'm thinking that it must be possible to rewrite the CASE statements as something that's easier to read, with less boilerplate.

I could do it with an IIF:

IIF(<column> IS NOT NULL, <expression>, NULL) AS <new_column>

but that still leaves both IS NOT NULL and NULL there.

Is there some construct FOO that would let me say

FOO(<nullcheck_expression>, <result_expression>)

and have that return the same thing as IIF(<nullcheck_expression> IS NOT NULL, <result_expression>, NULL)?

So you want [the opposite of `ISNULL`](http://stackoverflow.com/questions/19242782/is-there-a-opposite-function-to-isnull-in-sql-server-to-do-is-not-null)? I'm afraid that there is no other way than your `CASE`. But i find it very readable. — Tim Schmelter, Nov 02 '15 at 15:34
I was also trying to come up with a clever solution using `COALESCE` but was unable to do so. — Tim Biegeleisen, Nov 02 '15 at 15:37
@TabAlleman: i don't see how `NULLIF` would help to simplify this. — Tim Schmelter, Nov 02 '15 at 15:38
Maybe it can't, but he's simplified his code for this question, obviously, so in his real situation, maybe it can. — Tab Alleman, Nov 02 '15 at 15:39
@TabAlleman: but how? You have to specify the value that should be treated as `NULL` in the `NULLIF` function. But in this case `NULL` is simply `NULL` and **all other** values are `expression`. — Tim Schmelter, Nov 02 '15 at 15:40
Do you really need an explicit function? - The `ELSE NULL` is redundant, given that an [unmatched case will project NULL](http://stackoverflow.com/a/4626/314291) in any event — StuartLC, Nov 02 '15 at 15:40
@TimSchmelter: Yes, the opposite of `ISNULL` is a good description of what I want. The `CASE` statements are less readable when there's ten of them in a row and the´`s are longer :) — Tomas Aschan, Nov 02 '15 at 15:45
@TabAlleman: Actually, except for substituting `` for the real expressions that I select, I haven't changed or simplified anything. There's literally a bunch of `CASE` statements where there's exactly one `WHEN` clause, where that `WHEN` clause checks a column for `NULL`, and where the `ELSE` clause returns `NULL`. — Tomas Aschan, Nov 02 '15 at 15:47
@StuartLC: That's actually quite nice, and would help reduce the wall-of-boilerplate, but would obviously obfuscate the code a little to someone who doesn't know that. I'll see how it looks with a comment about it on top... ;) — Tomas Aschan, Nov 02 '15 at 15:48
Well if you just want to make the code shorter and more human-readable, you could always write a UDF that does what you want. — Tab Alleman, Nov 02 '15 at 15:55
@GiorgiNakeuri, here's the longest IIF that I used: `IIF(pcc.OrganizationNumber IS NOT NULL, LEFT(CAST(ABS(CAST(CAST(NEWID() AS varbinary) AS int)) AS varchar(max)), 6) + '-' + LEFT(CAST(ABS(CAST(CAST(NEWID() AS varbinary) AS int)) AS varchar(max)), 4), NULL) AS OrganizationNumber`. The first part is always ` IS NOT NULL`. — Tomas Aschan, Nov 03 '15 at 10:23
@TabAlleman: Sure, a UDF could do this. The question was also meant as "If I wrote a UDF for this, would I re-implement functionality that's already built-in?" — Tomas Aschan, Nov 03 '15 at 10:23
@TomasLycken, i.e. `WHEN IS NOT NULL THEN ` column is not participating in expression. Am I right? — Giorgi Nakeuri, Nov 03 '15 at 10:26
@GiorgiNakeuri: Yes, at least for the cases I have now that seems to be the case. — Tomas Aschan, Nov 03 '15 at 10:34
@TomasLycken, sorry, one more. What are types of columns and expressions? Are they always strings? — Giorgi Nakeuri, Nov 03 '15 at 10:44

score 1 · Accepted Answer · edited Nov 03 '15 at 08:32

1

I guess the answer to the question I posed is "no, there is no such function built-into SQL Server".

Instead, I ended up using the IIF-function, which, in conjunction with a few well-placed line breaks, is still much better than the original CASE statements. It's kind-of annoying to have IS NOT NULL sprinkled everywhere, but since they are all keywords that both MS SQL Management Studio and Visual Studio highlight in low-contrast gray, I can live with that.

edited Nov 03 '15 at 08:32

Tim Schmelter

450,073
74
686
939

answered Nov 02 '15 at 16:13

Tomas Aschan

58,548
56
243
402

`IIF` will be translated to a `CASE` statement. If you think that it makes your query more readable it's fine. I prefer to write sql that works in most rdbms. `IIF` works only in SQL-Server 2012 and newer. – Tim Schmelter Nov 03 '15 at 08:27

score 0 · Answer 2 · answered Nov 02 '15 at 16:58

0

You can try taking advantage of NULL behavior in SQL by concatenating the <expression> value with the <column> value.

SELECT LEFT(<expression> + <column>, n)

where n is the length of the <expression> value. If the <column> value is NULL, then it will concatenate to NULL, and the LEFT function will return a NULL. Otherwise, it will just take the <expression> from your <expression> + <column> concatenation.

answered Nov 02 '15 at 16:58

Sam Cohen-Devries

2,025
2
18
35

This is a good idea, but the result of `` is not fix-length, so I would have to either repeat it, or define an UDF for it - and in the latter case, an `IIF` or `NULLIF` construct is probably easier. – Tomas Aschan Nov 03 '15 at 07:06

score 0 · Answer 3 · answered Nov 03 '15 at 09:15

0

You can drop the ELSE NULL part which helps a bit:

 CASE WHEN <column> IS NOT NULL THEN <expression> END AS <new_column>

I also reformatted to a single line to take less vertical space.

answered Nov 03 '15 at 09:15

usr

168,620
35
240
369

score 0 · Answer 4 · answered Nov 03 '15 at 11:01

If you confirm that both columns and expressions are of varchar types then here is a little trick:

DECLARE @t TABLE(v varchar(10))

INSERT INTO @t VALUES('test'), (NULL)

SELECT LEFT(v, 0) + 'your expression' AS ExpressionResult
FROM @t

Output:

ExpressionResult
your expression
NULL

Explanation: if column contains some data it will return LEFT(v, 0) = '' and if it is null then it will return LEFT(v, 0) = NULL. This is shortest solution I've been able to find.

How do I propagate NULL values in a custom expression?

4 Answers4