Click here to Skip to main content
11,721,385 members (73,534 online)
Click here to Skip to main content

Tagged as

Using Coalesce() in sqlserver

, 11 Jun 2013 CPOL 73K 8
Rate this:
Please Sign up or sign in to vote.
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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

blnreddy
Web Developer trainer
India India
iam freelancer in software in dotnet....
now iam engaged with wipro through TSE at hyderabad

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 3 Pin
Umesh AP18-Aug-15 20:44
memberUmesh AP18-Aug-15 20:44 
GeneralMy vote of 5 Pin
Animesh Datta29-May-14 19:29
memberAnimesh Datta29-May-14 19:29 
GeneralMy vote of 5 Pin
ashumeerut12-May-14 7:05
professionalashumeerut12-May-14 7:05 
GeneralMy vote of 5 Pin
shravanyadav18-Mar-14 3:09
membershravanyadav18-Mar-14 3:09 
QuestionOne doubt Pin
♥…ЯҠ…♥23-Dec-13 17:52
professional♥…ЯҠ…♥23-Dec-13 17:52 
AnswerRe: One doubt Pin
blnreddy19-Mar-14 8:38
memberblnreddy19-Mar-14 8:38 
GeneralRe: One doubt Pin
♥…ЯҠ…♥19-Mar-14 18:24
professional♥…ЯҠ…♥19-Mar-14 18:24 
GeneralMy vote of 5 Pin
Balasubramanian T4-Aug-13 21:47
memberBalasubramanian T4-Aug-13 21:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150901.1 | Last Updated 11 Jun 2013
Article Copyright 2013 by blnreddy
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid