CODEWITHSUNDEEP
sqlsql-serverdatabase
malloa
malloa

Reputation: 59

How to use coalesce instead of IS NULL>

I don't know how to get the to the one retrieved by:

SELECT 
    AddressLine1 + ', ' + AddressLine2 AS Address,  
    City  
FROM
    [Person].[Address] 
WHERE 
    [AddressLine1] is not null 
    AND [AddressLine2] is not null

I need to use COALESCE() function. Every time I try it gets errors. How should I use coalesce() to get the same?

Upvotes: 2

Views: 1668

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Presumably, you want something like this:

SELECT COALESCE(AddressLine1, '') +', '+ COALESCE(AddressLine2, '') AS Address, City
FROM [Person].[Address];

That way you can do the string concatenation, even when the values are NULL.

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116190

You cannot do that. COALESCE is a function that returns the first non-null value.

I think you intended to write this query:

SELECT AddressLine1 +', '+ AddressLine2 AS Address, City FROM [Person].[Address] 
WHERE COALESCE([AddressLine1], [AddressLine2]) is not null

It will have the same effect as:

SELECT AddressLine1 +', '+ AddressLine2 AS Address, City FROM [Person].[Address] 
WHERE [AddressLine1] is not null OR [AddressLine2] is not null

The difference between OR in this query vs AND in yours is small, but oh so important. Apart from that, it's not much shorter, faster or better readable, so I don't think this is a good use case for COALESCE.

Upvotes: 4

Related Questions