ISNULL/COALESCE counterpart for values that are not NULL

Question

Is there a function that is similar to COALESCE but for values that are not NULL?

I need to replace a return value of a scalar-valued-function with another value if it returns the string literal N/A. If it would return NULL i could use:

SELECT COALESCE(dbo.GetFirstSsnInFromSsnOut(RMA.IMEI), RMA.IMEI) AS [IMEI to credit]
     , OtherColumns
FROM dbo.TableName

But since it returns N/A this doesn't work. I could also use:

SELECT CASE WHEN dbo.GetFirstSsnInFromSsnOut(RMA.IMEI)='N/A' 
         THEN RMA.IMEI 
         ELSE dbo.GetFirstSsnInFromSsnOut(RMA.IMEI)
       END AS [IMEI To Credit]         
    , OtherColumns
FROM dbo.TableName

But that would be inefficient since it needs to execute the function twice per record.

Note that this query is in a table-valued-function.

But can't you just modify the function `GetFirstSsnInFromSsnOut` itself to return NULL instead of N/A ? — Stephen Byrne, Nov 15 '13 at 10:27
@StephenByrne: That would cause other problems/work. So i've asked first if there is a way to solve this without the "breaking change". The method is already used in some excel addins that i would have to modify then. — Tim Schmelter, Nov 15 '13 at 10:28
What about creating another function that wraps `GetFirstSsnInFromSsnOut` and changes N/A to `NULL`? Doing that, you could use `COALESCE` and still have only one call to `GetFirstSsnInFromSsnOut` — neutrino, Nov 15 '13 at 10:31

Andrey Morozov · Accepted Answer · 2015-01-19T18:57:04.270

10

Perhaps there you can use NULLIF function, for example:

SELECT ISNULL(NULLIF(dbo.GetFirstSsnInFromSsnOut(RMA.IMEI), 'N/A'), RMA.IMEI) AS [IMEI to credit]
     , OtherColumns
FROM dbo.TableName;

edited Jan 19 '15 at 18:57

answered Nov 15 '13 at 10:34

Andrey Morozov

7,839
5
53
75

2

@TimSchmelter - This may well execute the function twice anyway. [`ISNULL` is generally better than `COALESCE` for this.](http://connect.microsoft.com/SQLServer/feedback/details/336002/unnecessarily-bad-performance-for-coalesce-subquery) – Martin Smith Nov 15 '13 at 10:49
@MartinSmith: Really, why? Can i prevent that in some way? – Tim Schmelter Nov 15 '13 at 10:52
1

See the connect item linked above. If the plan shows the function referenced twice because of how `COALESCE` gets expanded to `CASE` then you can replace `COALESCE` with `ISNULL` – Martin Smith Nov 15 '13 at 10:53
1

I changed COALESCE to ISNULL (I personally use ISNULL in most cases) – Andrey Morozov Nov 15 '13 at 10:56
@MartinSmith: Interesting, thanks for the info. I normally use `COALESCE` everywhere. Easy to reproduce the bug since `SELECT coalesce(xyz, 0) FROM sys.objects` produces two errors instead of one. – Tim Schmelter Nov 15 '13 at 10:57
I use COALESCE only in cases when count of parameters > 2 – Andrey Morozov Nov 15 '13 at 10:59
@Andrey: Why (apart from this reason)? `COALESCE` is ANSI standard as opposed to `ISNULL`. – Tim Schmelter Nov 15 '13 at 11:00
The isnull function requires exactly 2 argument(s), but COALESCE can accept more than 2 parameters – Andrey Morozov Nov 15 '13 at 11:01
2

Seems that `COALESCE` always gets expanded to `CASE`. Was an eye opener for me! http://msdn.microsoft.com/en-us/library/ms190349.aspx – Stephen Byrne Nov 15 '13 at 12:31
OMG! I did't know that! Thanx! – Andrey Morozov Nov 15 '13 at 12:44
@StephenByrne: Thanks for the link to MSDN which also mentions this behaviour: _"This means that the input values (expression1, expression2, expressionN, etc.) will be evaluated multiple times. Also, in compliance with the SQL standard, a value expression that contains a **subquery** is considered non-deterministic and the subquery **is evaluated twice**. In either case, different results can be returned between the first evaluation and subsequent evaluations. For example, when the code COALESCE((subquery), 1) is executed, the subquery **is evaluated twice** .... "_ – Tim Schmelter Nov 15 '13 at 15:02
@TimSchmelter - it's amazing what we learn when we RTFM :) I used `COALESCE` all the time up until today, purely because I thought I was being cool with my "ANSI Compatible" SQL...so thanks to *you* for the question that led to this revelation for me! – Stephen Byrne Nov 15 '13 at 15:09

score 5 · Answer 2 · answered Nov 15 '13 at 10:34

5

If you can't change GetFirstSsnInFromSsnOut to return Null-Values then try this:

SELECT COALESCE(dbo.GetFirstSsnInFromSsnOut(NULLIF(RMA.IMEI, 'N/A')), RMA.IMEI) AS [IMEI to credit] , OtherColumns FROM dbo.TableName

answered Nov 15 '13 at 10:34

deterministicFail

1,271
9
28

Thanks to all, you were the fastest, but Andrey's answer contains the the function and link i've searched. I have upvoted all three anyway. – Tim Schmelter Nov 15 '13 at 10:50

score 5 · Answer 3 · answered Nov 15 '13 at 10:34

The function nullif returns null if its arguments are equal, and its first argument otherwise. Combine with coalesce for fun and profit:

select
    coalesce(
        nullif(dbo.GetFirstSsnInFromSsnOut(RMA.IMEI), 'N/A'),
        RMA.IMEI
    ) as [IMEI To Credit]
    , OtherColumns
from dbo.TableName

ISNULL/COALESCE counterpart for values that are not NULL

3 Answers3