Feeds:
Posts
Comments

Posts Tagged ‘NULLIF’

In legacy data, it is very common that you find a lot of unnecessary NULL values and you need to do massage to present this data, whether it is a report or an email. Generally, we use few techniques to avoid NULL and replace it with any characters or numbers.
Before proceeding with the solution, I would like to create a sample to demonstrate the solution.

SAMPLE :

DECLARE @tbl_sample TABLE
(
 [ID] INT,
 [Name] VARCHAR(50),
 [Code] INT
)

INSERT INTO @tbl_sample VALUES(1,'Bob',1234)
INSERT INTO @tbl_sample VALUES(2,'Sandra',NULL)
INSERT INTO @tbl_sample VALUES(3,'Mike',NULL)

SELECT
*
FROM
@tbl_sample
--OUTPUT

return_result_0.1.1

METHOD 1 :
Given below is the script to replace NULL using ISNULL (a SQL Server built-in function).

SELECT
 [ID]
,[Name]
,ISNULL([Code],0) AS [Code]
FROM @tbl_sample
--OUTPUT

return_result_0.1.2

METHOD 2 :
Given below is the script to replace NULL using COALESCE (a SQL Server built-in function).

SELECT
 [ID]
,[Name]
,COALESCE([Code],0) AS [Code]
FROM @tbl_sample
--OUTPUT

return_result_0.1.2

METHOD 3 :
Given below is the script to replace NULL using CASE STATEMENT (a SQL Server expression).

SELECT
 [ID]
,[Name]
,CASE WHEN [Code] is NULL THEN 0 ELSE [Code] END 
As [Code]
FROM @tbl_sample
--OUTPUT

return_result_0.1.2

Advertisements

Read Full Post »