Fun with SQL

Our business report writer had asked one of my developers for a multiple parameter function to use for her SSRS report. The I/O is as below:

Input: ‘Bala|Summa|Peela|Vidathe’

Output: ‘Bala’,’Summa’,’Peela’,’Vidathe’

Here is the code if you want a RegEx like function in SQL.

/****** Object: UserDefinedFunction [dbo].[SplitFields] Script Date: DD/MM/YYYY ******/

IF EXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'[dbo].[SplitFields]’)ANDtypein(N’FN’,N’IF’,N’TF’,N’FS’,N’FT’))

DROP FUNCTION [dbo].[SplitFields]

GO

USE [A DB NAME]

GO

 

/****** Object: UserDefinedFunction [dbo].[SplitFields] Script Date: DD/MM/YYYY ******/

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

 

CREATEFUNCTION [dbo].[SplitFields] ( @InputList nvarchar(2000), @Delimiter nvarchar(5))

RETURNS NVarchar (3000)

AS

BEGIN

DECLARE @RtnList nvarchar(3000)

SET @RtnList =””+REPLACE(@InputList, @Delimiter,”’,”’)+””

RETURN @RtnList

END

GO

 

/****** Test script******/

USE [KenticoCMS]

GO

DECLARE @VARIABLE NVARCHAR(3000)

Exec @VARIABLE = [dbo].[SplitFields] ‘Bala|Summa|Peela|Vidathe’,’|’

PRINT @VARIABLE

GO

 

 

Backstage story: 🙂

Since my developer was stumped for several hours, I offered to help. I have not played with T-SQL in many moons. So, this looked like a good mini-challenge.

He had already started with a while loop in a function. The function itself was having issues parsing and formatting.

For my convenience, I replaced the while loop with a REPLACE function. While the code executed well, I was having problem with the return value, which was a single character. It took me whole 20 minutes before figured out the problem. I was returning a single NVarchar instead of the whole string.

Advertisements