Learn Sql Server   Learn Sql Theory   Learn Sql

 
Chapter 1
Chapter 2
Chapter 3

Sql Server Joins

Sql Server Inner Join

Sql Server Left Outer Join

Sql Server Right Outer Join

Sql Server Full Outer Join

Sql Server Cross Join

Sql Server Self Join

Sql Server @@IDENTITY

Sql Server Scope Identity

Sql Server ISNULL

Sql Server COALESCE

Sql Server Output Inserted

Output clause Deleted

Sql Server Temporary Tables

Sql Server Newid

Sql Server Replace

Sql Server SubQuery

Sql Server Begin End

Sql Server Try Catch

Sql Server Transaction

Sql Server Bulk Insert

Sql Server While Statement

Sql Server Constraints

Sql Server Identity Insert

Sql Server Goto Statement

Sql Server Converting Data Types

Performing Date Conversions

sql server select into

sql server select case

Chapter 4
Chapter 5
Chapter 6
Chapter 7
Chapter 8
Chapter 9
Chapter 10
Chapter 11
Chapter 12
Chapter 13
Chapter 14
Chapter 15
Chapter 16

COALESCE Function in sql server

The COALESCE function returns the first non-NULL value from a provided list of expressions. The COALESCE function is passed an undefined number of arguments and it tests for the first nonnull expression among them.If all arguments are NULL then COALESCE returns NULL.

Sponsored links

The syntax is as follows:

COALESCE(expression [,...n])

Example

so we have table tbl_employee with some data


empidNamePhonenoMobile_No
1jak01123504531null
2harrynull9888434243
3marry02270261069null
4garrynull9780234567

We can use the COALESCE function to achieve our goal:

SELECT Name, COALESCE(Phoneno, Mobile_No) as Contact_Phone FROM tbl_employee;

Result using COALESCE Function

NameContact_Phone
jak01123504531
harry9888434243
marry02270261069
garry9780234567

So we have learned The COALESCE function which returns the first non-NULL expression among its arguments.
The coalesce function in sql server will compare each value, one by one.
It is the same as the following CASE statement:

SELECT CASE ("colmn_name")
WHEN "expr1 is not NULL" THEN "expr 1"
WHEN "expr2 is not NULL" THEN "expr 2"
...
[ELSE "NULL"]
END
FROM table_name

Working of above COALESCE function example

In this example we have table tbl_employee with data .In this table we have column phoneno and mobile_no with null value .and if you want to show contact_no may be it is phone no or mobile_no it comes to single column then we use the COALESCE function in sql server.which check the null value from the column which specify and return the non null able value exlain in above example.

Sponsored Links