65.9K
CodeProject is changing. Read more.
Home

Using Coalesce() in sqlserver

starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.68/5 (22 votes)

Jun 11, 2013

CPOL
viewsIcon

140516

SQL Server built-in functions Coalesce()

Introduction

When we have multi-value attribute with single or more null values in a Table, the Coalesce() function is very useful.

Using the Code

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 number from employee 

It returns:

id

Name

Contactnumber

101

Albert

999999

102

khan

123455

103

victor

112121

104

lovely

1897321

Thank you!