Saturday, 12 August 2017

coalesce function in SQL server with example

Coalesce Function in SQL Server

coalesce function-in-SQL-server-with-example

Coalesce function will use in very rare case in real time, but it is very important interview question in sql server. Both Isnull function and Coalesce function works similar, so there are chances to get confuse. Let we discuss about Coalesce function with example so that you can remember easily.

What is Coalesce function in sql server?

Coalesce function is used to "returns the first nonnull expression among its arguments"



what is syntax of Coalesce function in sql server?

COALESCE( expression1, expression2, ... expression_n )

Coalesce function in sql server with example

Run the below query in sql server management studio (ssms)

SELECT COALESCE(NULL, NULL, 'FreshersJunction', NULL, 'SQL Server')

The Output is: FreshersJunction

By the above example we can clearly understand that Coalesce function returns the first nonnull expression among its arguments in sql server.

Now we will try some more examples for coalesce function in SQL Server

SELECT COALESCE(NULL, NULL, NULL, NULL, 'SQL Server')

The output is : SQL Server

SELECT COALESCE('Function', NULL, NULL, NULL,NULL)

The output is : Function

clearly observer the functionality of coalesce function, its looking like case statement, right?

Yes, coalesce function is shortcut for the CASE statement (only in the case of checking not null value). See the below example

CASE

WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END

I hope now you have clear understanding on coalesce function.

Now, in what scenarios we will use coalesce function in sql sever with example.


Create the table by using below query

Create table Employee_Contact_Number
(Eid int, Ename nvarchar(255), Mobile_Number bigint,
Home_Number bigint, Office_Number bigint)

Insert the values by using below query in sql server

insert into Employee_Contact_Number values (101, 'Mahesh', 9999999999, Null, null)
insert into Employee_Contact_Number values (102, 'Pawan', Null, 0801234, null)
insert into Employee_Contact_Number values (103, 'Venkat', null, Null, 00912349686)
insert into Employee_Contact_Number values (104, 'Vasu', 123467890, Null, 0079911129)
insert into Employee_Contact_Number values (105, 'Khadar', 90767433893, 56972736333, 040782974645)

Now check the table

select * from Employee_Contact_Number

Output:

Eid
Ename
Mobile_Number
Home_Number
Office_Number
101
Mahesh
9999999999
NULL
NULL
102
Pawan
NULL
801234
NULL
103
Venkat
NULL
NULL
912349686
104
Vasu
123467890
NULL
79911129
105
Khadar
90767433893
56972736333
40782974645

Now from the above table we should get at least one contact number for each employee and 1st preference is Mobile_Number and 2nd preference is Home_Number and 3rd preference is Office_Number.

In the above scenario coalesce function will work exactly

select Eid, Ename, coalesce(Mobile_Number,Home_Number,Office_Number) as Contact_Number from Employee_Contact_Number

The output for the above query is

Eid
Ename
Contact_Number
101
Mahesh
9999999999
102
Pawan
801234
103
Venkat
912349686
104
Vasu
123467890
105
Khadar
90767433893

Now you understood scenarios of coalesce function in sql server, right?

To get regular job updates and technical sql server interview questions, share your email id with us.

Enter Your Mail ID Here and confirm

If you have any queries related to SQL Server, please comment here. We will give you update as soon as possible.

You May also search in Google about coalesce function as:
coalesce function in sql server with example
What does coalesce do in SQL?
coalesce sql server example
coalesce function in sql server 2012
coalesce function in sql server 2008 with example

Freshers Registration Link

share facebook

Enter Your Email below to get Daily Job Alert in Your Mailbox