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
– Stephen Byrne, Commented Nov 15, 2013 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
– Tim Schmelter, Commented Nov 15, 2013 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
– neutrino, Commented Nov 15, 2013 at 10:31

Andrey Morozov · Accepted Answer · 2015-01-19 18:57:04Z

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, 2015 at 18:57

answered Nov 15, 2013 at 10:34

Andrey Morozov

8,0175 gold badges59 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Martin Smith Over a year ago

@TimSchmelter - This may well execute the function twice anyway. ISNULL is generally better than COALESCE for this.

Tim Schmelter Over a year ago

@MartinSmith: Really, why? Can i prevent that in some way?

Martin Smith Over a year ago

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

Andrey Morozov Over a year ago

I changed COALESCE to ISNULL (I personally use ISNULL in most cases)

Stephen Byrne Over a year ago

Seems that COALESCE always gets expanded to CASE. Was an eye opener for me! msdn.microsoft.com/en-us/library/ms190349.aspx

|

deterministicFail · Accepted Answer · 2013-11-15 10:34:06Z

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, 2013 at 10:34

deterministicFail

1,2819 silver badges28 bronze badges

1 Comment

Tim Schmelter Over a year ago

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.

Simon · Accepted Answer · 2013-11-15 10:34:55Z

5

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

answered Nov 15, 2013 at 10:34

Simon

5062 silver badges8 bronze badges

Collectives™ on Stack Overflow

ISNULL/COALESCE counterpart for values that are not NULL

3 Answers 3

12 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

12 Comments

1 Comment

Comments

Related