ansaurus

Question

Inverse of COALESCE

Answer 1

+1 A:

No, the closest you an get is NULLIF(), but that's not what you want. I'd just stick to using the OR statement here.

Dave Markle 2010-04-21 13:38:37

Actually, NULLIF returns NULL if the two input-parameters are the same. I don't see any way to achieve this with cascaded NULLIFs.

ercan 2010-04-21 13:59:40

Thanks. I misspoke.

Dave Markle 2010-04-21 15:31:54

Answer 2

+2 A:

Here is a moderately unpleasant way of doing it:

set ansi_nulls off
if (null in (a, b, c, d, e) print 'got a null'
set ansi_nulls on

Alex K. 2010-04-21 14:24:31

~shudders at the implications~

Jeremy 2010-04-21 14:28:37

A stern caution from Microsoft that NULL can produce unexpected results in this scenario: http://msdn.microsoft.com/en-us/library/ms177682.aspx

John K 2010-04-21 14:29:31

+1 for admitting that this is moderately unpleasant :)

ercan 2010-04-21 14:35:19

Answer 3

A:

Since NULLs propogate you could do:

(cola + colb + colc) is null

assuming all compatible data types

Jeremy 2010-04-21 14:27:22

That's why I put the constraint **of any type** in the post ;)

ercan 2010-04-21 14:33:29

Maybe you could coerce them into a single type, since the values themselves don't matter for the test. Maybe a nullable bit?

tloflin 2010-04-22 16:46:08

Answer 4

A:

How about ...

SELECT
CASE WHEN NULLIF(ISNULL(@testA, 1), @testA) 
        + NULLIF(ISNULL(@testB, 1), @testB) 
        + NULLIF(ISNULL(@testC, 1), @testC) > 0
    THEN 'Got NULL'
    ELSE 'NO NULL'
END

al 2010-04-22 16:41:36

This takes care of the **"of any type"** constraint, but for the sake of readability, I would prefer `@testA IS NULL OR @testB IS NULL OR @testC IS NULL`

ercan 2010-04-23 08:16:20

ansaurus

tags:

views:

answers:

Inverse of COALESCE

related questions