SQL Server: Find First NOT NULL Column using a COALESCE Function

The COALESCE() is a one of my favourite function in SQL Server.
Using this function, we can check first NOT NULL column or field out of all consecutive columns.
We can pass any number of parameters and it returns the first NOT NULL value.

The COALESCE is ANSI standard function and internally it is working like a CASE..WHEN expression.

For example:
We have three different PhoneNumber column for single User and may be User has only one or two PhoneNumber and now requirement is to fetch at least one PhoneNumber from this three different PhoneNumber.

We can easily solve this kind of problem using COALESCE() and it doesn’t need to write big CASE..WHEN expression.

Below is a small demonstration of this:

First, create sample table and data:

SQL Server NULL Columns

You can find some NULL field in the above result.

Now use COALESCE() to SELECT first NOT NULL PhoneNumber:

The Result:

SQL Server Coalesce Function

Anvesh Patel

Leave a Reply

6 Comments on "SQL Server: Find First NOT NULL Column using a COALESCE Function"

Notify of
avatar
Sort by:   newest | oldest | most voted
norman
Guest

Hey! This post couldn’t be written any better! Reading through this post reminds me of my old room mate!

He always kept chatting about this. I will forward
this page to him. Pretty sure he will have a
good read. Thank you for sharing!

Valen
Guest

I really appreciate this post. I have been looking everywhere for this! Thank goodness I found it on Bing. You’ve made my day! Thx again!

Denial
Guest

Enjoyed examining this, very good stuff, appreciate it.

Robin
Guest

I like this web site very much, Its a real nice situation to read and get info . “If at first you don’t succeed, you’re running about average.” by M. H. Alderson.

Monika
Guest

You should take part in a contest for one of the best blogs on the web. I will recommend this site!

trackback

thanks for sharing… keep it up Anvesh!

wpDiscuz