Scalar Function ISEmpty in SQL Server

When we are working with data exchange from flat files or excel files to our relational tables, sometimes, empty records get inserted instead. Since, in SQL Server null and empty records are different, and if we need to have a scalar function that can be used to replace empty records then it can be done as below. Some would wonder as why we would need ISEmpty function when we already have REPLACE function in place, the argument for that is, let's say we have a table with column as Name and we are having all sorts of names e.g.('John','Joe ',' Ken',' '). If we use replace here, say we replace space with 'N/A' then records having name 'Joe ' would be 'JoeN/A' , ' Ken' would be 'N/AKen' which is not what we might be looking for. Function below would replace the records that are completely empty.

 

CREATE FUNCTION [dbo].[IsEmpty] (

@Data varchar(100),

@IsEmptyData varchar(100)

)

RETURNS varchar(100)

AS

BEGIN

 

 

IF (@Data = ' ')

BEGIN

SET @Data = @IsEmptyData

END

 

 

RETURN (@Data)

 

 

END

 

GO

Lets test the funtion:

Declare @t as table(ID int,name varchar(10))

Insert @T values(1,'a')

insert @t values(2,' ')

Select id,dbo.Isempty(name,'N/A') from @t

 

 

Tags: , ,

SQL Server | SQL Tricks | Scalar Function