Skip to main content

Coalesce function

Coalesce function returns the first non-null value among the arguments.

Syntax:

Coalesce (expression [,..n])

Here is example using Coalesce function

Example 1

DECLARE @Str1 varchar(10),@str2 varchar(20),@Str3 varchar(20)

SET @Str2='Sql' , @Str3='Server'

SELECT COALESCE(@Str1,@str2,@Str3) As [Coalesce]


In above example @Str2 value is ‘Sql’ , @str3 value is ‘Server’  and @str1 values is Null because it not assigned any value .

Output:

It return’s “Sql” because Coalesce function return’s first non null value.








Example 2:

Coalesce in select statement.

IF OBJECT_ID('Employee','U') IS NOT NULL
DROP TABLE Employee


CREATE TABLE Employee
(
  ID INT IDENTITY(1,1) PRIMARY KEY ,
  NAME VARCHAR(20),
  SALARY INT
)

INSERT INTO Employee  (NAME,SALARY) VALUES ('Rakesh',5000),(NULL,6000),('Naresh',7000) ,(NULL,8000)


SELECT * FROM Employee










In above Query Result ID 2, 4 of Name Column is null.   We have to replace Name Column null value to some other value (“Madhu”) using select statement.

SELECT ID,COALESCE(NAME,'Madhu') AS Name,SALARY FROM Employee

Output:









Example 3:

Coalesce in where condition.

DECLARE @Lod_ID int =null

SET @Lod_ID =3

SELECT * FROM Employee where ID= COALESCE(@Lod_ID,ID)
Output:

It only select ID value 3 row .because of in above @Lod_ID variable is set to 3 values . Coalesce funtion returns first non-null value baecuse it’s selects only 3 of ID colums rows only.  





Example 4:

DECLARE @Lod_ID int =null

SELECT * FROM Employee where ID= COALESCE(@Lod_ID,ID)

In above example if we are not set any value to @Lod_ID variable . we are get all row from Employee table .

Comments

Post a Comment

Popular posts from this blog

CONCAT FUNCTION IN SQL SERVER 2012

Concat function is used concatnating the values among the different arguments. Syntax: Concat([String1],[String2]..[StringN]);  DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SET @LName = 'Kalluri' ; SELECT CONCAT ( @FName , ' ' , @LName ) as [FullName] ; Output: Rakesh Kalluri --Before SQL 2012 Versions we can use like this way DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SET @LName = 'Kalluri' ; SELECT @FName + ' ' + @LName as [FullName] ; Output: Rakesh Kalluri --But Before SQL 2012 Versions there are 2 problems when we  contacting . --1.Need to replace NULL Value to Empty.if we directly concat NULL Value the  Entire  result also NULL. --Before 2012: DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SELECT @FName + ' ' + @LName a...
Post a Comment
Read more

Rank Functions in SQL SERVER

1 . ROW_NUMBER () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns the sequantial number of a row within the a partition of result set at 1 for the first row of the each partition. 2. RANK () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns rank for rows within the partition of result set. 3. DENSE_RANK () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns rank for rows within the partition of result set.With out any gaps in the ranking. 4. NTILE ( INTEGER_EXPRESSION ) OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Distributes the rows in an ordered partition into a specified number of groups. Examples: --create Employee table create table Employee (                 EmpId int identity ( 1 , 1 ) primary key ,              ...
1 comment
Read more