CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005sql-function
Tim Schmelter
Tim Schmelter

Reputation: 460058

ISNULL/COALESCE counterpart for values that are not NULL

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.

Upvotes: 3

Views: 1790

Answers (3)

Andrey Morozov
Andrey Morozov

Reputation: 7969

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;

Upvotes: 10

Simon
Simon

Reputation: 506

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

Upvotes: 5

deterministicFail
deterministicFail

Reputation: 1285

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

Upvotes: 5

Related Questions