What is Coalesce Function in Sql Server? How it is comfortable with Optional Search

What is Coalesce Function in Sql Server? How it is comfortable with Optional Search

The COALESCE function present in SQL .It use to returns the first non-NULL expression among its arguments. The syntax for COALESCE function is as follows

  Select * from "table name" where
  WHEN "expression 1 is not NULL" THEN "expression 1"
  WHEN "expression 2 is not NULL" THEN "expression 2"
  ...
  [ELSE "NULL"]
  END

Create table ContactDetails……………………………………..

create table ContactDetails(SrNo int identity(1,1) primary key,Name nvarchar(50) unique,Business_MobileNo nvarchar(50),Office_MobileNo nvarchar(50),Home_MobileNo nvarchar(50))

Note: we want to find out the best way to contact no each Employee according to the following rules:

1. If an employee has a business mobileno, use the business mobileno.

2. If an employee does not have a business mobileno and has a office mobile no, use the office mobile number.

3. If an employee does not have a business mobile no, does not have an office mobile no, and has a home mobile no, use the home mobile no number.

Query for above concept…………………………………….

select SrNo,Name,coalesce(Business_MobileNo,Office_MobileNo,Home_MobileNo)ContactNo from ContactDetails

You can also use above concept without coalesce function…………………………………….

select SrNo,Name as EmployeeName, ISNULL(Business_MobileNo,ISNULL(Office_MobileNo,Home_MobileNo))from ContactDetails

select  SrNo,Name, Business_MobileNo=case when ISNULL(Business_MobileNo,'')='' then case when ISNULL(Office_MobileNo,'')='' then Home_MobileNo else Office_MobileNo end else Business_MobileNo end from ContactDetails

Another similar query with coalesce function

create table IssueBook (Id int ,BookName nvarchar(50))

insert into IssueBook values(1,'Java')

insert into IssueBook values(1,'Html')

Select * from IssueBook

Create function SearchBook with coalesce function

 alter function SearchBook(@id int) returns varchar(max)

 as

 begin

 declare @book varchar(max)

 select @book=coalesce(@book + ',','') + bookname from issuebook where id=@id

 return @book

 end

 select  distinct dbo.SearchBook(1) from IssueBook

Create function SearchBooks without coalesce function

create function SearchBooks(@id int) returns varchar(500)

as

begin

declare @data varchar(500)=''

select @data +=bookname+',' from Issuebook where id=@id

return left(@data,len(@data)-1)

end

select dbo.SearchBooks(1)

Next Topic(How it is comfortable with Optional Search)