Monday, September 12, 2011

ISNULL, COALESCE works with both INT and VARCHAR column

There was a question saying ISNULL won't work for VARCHAR columns. I thought I would provide a very basic sample where ISNULL works with string column as well.

--Sample table with a INT and a VARCHAR column
Create table tbltest
(
intSno int,
strName Varchar(10)
)
GO

--Key-in NULL for both columns
Insert into TblTest Values (null, null)
GO

Sample queries:

Select isnull(intSno, 0), isnull(strName, 0) from tblTest
GO

SELECT COALESCE(intSno, 0), COALESCE(strName, 0) from tblTest
GO

--ISNULL on a varchar column replacing with a non numeric value
SELECT ISNULL(strName, '/0') FROM tblTest
GO

--Coalesce on a varchar column
SELECT COALESCE(strName, '/0') FROM tblTest
GO

No comments: