Using Coalesce() in sqlserver

If you consider the below facts placed in a employee table with Id, Name, ph_no, Alt_no, Office no.

id Name Ph_ no Alt_ no Office no
101 Albert 999999 456453 321333
102 khan null null 123455
103 victor 112121 null null
104 lovely null null 1897321

The above Employee table may have single value or three values. If it has single value, then it fills null values with remaining attributes.

When we retrieve the number from employee table, that number Should Not be Null value. To get not null value from employee table, we use Coalesce() function. It returns the first encountered Not Null Value from employee table.

select id , name ,coalesce(Ph_no,Alt_no,Office_no)as contact numberfrom employee

It returns:

id Name Contactnumber
101 Albert 999999
102 khan 123455
103 victor 112121
104 lovely 1897321

Thank you!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s