Friday, August 19, 2011

COALESCE In SQL-SERVER

Posted by Rahul Kharde at 1:38 AM
COALESCE
Returns the first nonnull expression among its arguments
Syntax
COALESCE (expression 1, expression 2 . . . . expression N)
Is a placeholder indicating that multiple expressions can be specified? All expressions must be of the same type or must be implicitly convertible to the same type.
  1. If first Column Not NULL and second column is NULL, then it return first column value
  2. If first column is NULL and second column is NOT NULL, then it return second column value
  3. If both are column Not NULL, then it return first column value
  4. If both are NULL ,it return NULL value
In this example, Table is shown three columns with information about an employee's name it phone and mobile number. 

CREATE TABLE Employee
(
       empID INT IDENTITY(1,1) NOT NULL,
       empName VARCHAR(255) NULL,
       Phone INT NULL,
       Mobile INT NULL
)
 
INSERT INTO Employee(empName,Phone,Mobile)VALUES('abc',1234567890,null)
INSERT INTO Employee(empName,Phone,Mobile)VALUES('pqr',null,987654321)
INSERT INTO Employee(empName,Phone,Mobile)VALUES('mno',55534343,342232323)
INSERT INTO Employee(empName,Phone,Mobile)VALUES('xyz',null,null)


The following example shows how COALESCE selects the data from the first column that has a nonnull value.

SELECT empName,COALESCE(Phone,Mobile) as empContactno FROM Employee

empName
empContactno
abc
1234567890
pqr
987654321
mno
55534343
xyz
NULL

That in the return type, it states that COALESCE returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable. Because your statement contains a numeric value, your output is getting implicitly cast to int, which is the highest precedence data type out of the arguments you supplied. If you surround 8 in single quotes, it will type it as a string value (probably varchar), and the empty string value will be cast to that instead, but int has a higher precedence than varchar.



If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top