CODEWITHSUNDEEP
sqlsql-servert-sqlsentence
Daniel Veintimilla Tenorio
Daniel Veintimilla Tenorio

Reputation: 41

Concatenate a column only if its not NULL

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.

Upvotes: 2

Views: 12029

Answers (3)

Kalju Pärn
Kalju Pärn

Reputation: 31

WITH PERSON AS (
SELECT * FROM (VALUES
 ('Name1' , '12345' , Null , Null , Null),
 ('Name2' , Null , '54774' , Null , Null),
 ('Name2a', Null , '54774' , Null , '4356'),
 ('Name3' , Null , Null , '77841' , Null))
 s( NAME , PHONE , PHONE1 , PHONE2 , PHONE_CONCAT )) 
SELECT NAME ,TRIM(CONCAT( PHONE + ' ', PHONE1 + ' ', PHONE2 + ' ', PHONE_CONCAT)) FROM PERSON

Will be:

NAME   
------ ----------------------
Name1  12345
Name2  54774
Name2a 54774 4356
Name3  77841

(4 rows affected)

Upvotes: 0

sagi
sagi

Reputation: 40481

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,'')

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 8

Related Questions