Combining two columns of a table into a single column

Question

In my Employee table there are two columns named Empid, EmpNm

Empid EmpNm
001   Null
002   Ram
Null  Akhaya
Null  Tom
005   Satya

Two combine both the column I am using the query like

SELECT (Empid + EmpNm) FROM Employee

And the result is like

Null
002Ram
Null
Null
005Satya

But my requirement is like

001
002Ram
Akhaya
Tom
005Satya

Is there any suggestion to this query from your side ?

Answer 1

try this,it will help you..

SELECT (ISNULL(Empid,'') +ISNULL(EmpNm,'')) AS name FROM dbo.Employee

Answer 2

When concatenating null with something the result will be null. You can use coalesce to take care of that. Coalesce will return the first non null argument.

This will return a result set

select coalesce(Empid, '') + coalesce(EmpNm, '') as Empid
from Employee

This will update the Empid column in table Employee

update Employee 
set EmpID = coalesce(Empid, '') + coalesce(EmpNm, '')