Concatenate a column only if its not NULL

Question

i have a SQL Server table like this:

-------------------------------------------------------------------
PERSON
-------------------------------------------------------------------
   NAME   |   PHONE   |   PHONE1   |   PHONE2   |   PHONE_CONCAT  
-------------------------------------------------------------------
   Name1  |   12345   |   Null     |   Null     |   Null
   Name2  |   Null    |   54774    |   Null     |   Null
   Name3  |   Null    |   Null     |   77841    |   Null
-------------------------------------------------------------------

What I want to do is concatenate into PHONE_CONCAT the columns PHONE, PHONE1, and PHONE2 just when the value for one of those columns is !=null. So, in this case, the final value for the PHONE_CONCAT column will be:

------------------
PERSON
------------------
   PHONE_CONCAT  
------------------
   12345
   54774
   77841
 ------------------

Could u please help me perform this action?

I want to update the PHONE_CONCAT value, so I will need to execute an update on each row in table.

Answer 1

Is this what you want?

select coalesce(t.phone, t.phone1, t.phone2) as phone_concat
from t;

This returns the first phone number that is not NULL. That seems like the simplest way to get your desired result.

Answer 2

As a select statement :

SELECT COALESCE(t.phone,'') + COALESCE(t.phone1,'') + COALESCE(t.phone2,'') 
FROM PERSON t

Or if you want to update it:

UPDATE PERSON t
SET t.PHONE_CONCAT = COALESCE(t.phone,'') + COALESCE(t.phone1,'') + COALESCE(t.phone2,'')