Split String in SQL Server UDF with custom delimiter

Objective:
Ok friends, suppose you are writing a stored procedure and you have no idea how many parameter values to pass to it. Now what to do?

Solution:
There are a few options you can try here. The first option, you can write an iterative procedure that calls the stored procedure several times; however, this option makes unnecessary calls to your stored procedure and reduces the performance of the application. Alternatively, you can pass one single string to the stored procedure with a delimiter and split the string inside the stored procedure. This will give you a result set in tabular format providing fewer stored procedure calls and better performance that the first option.

Here I am going to show you how to pass a string and get the result set.

1. Create one User Defined Function (UDF) that accepts two parameters
1. StringToSplit – String you want to split
2. DelimiterToSplit – Delimiter

The function will return one table that includes a column called “DataItem”.

CREATE FUNCTION Func_SplitString(

@StringToSplit VARCHAR(MAX),

@DelimiterToSplit VARCHAR(5) = ','

) RETURNS @ReturnList TABLE (DataItem VARCHAR(100))

BEGIN

DECLARE @sItem VARCHAR(8000)

WHILE CHARINDEX(@DelimiterToSplit,@StringToSplit,0) <> 0

BEGIN

SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@StringToSplit,1,CHARINDEX(@DelimiterToSplit,@StringToSplit,0)-1))),

@StringToSplit=RTRIM(LTRIM(SUBSTRING(@StringToSplit,CHARINDEX(@DelimiterToSplit,@StringToSplit,0)+LEN(@DelimiterToSplit),LEN(@StringToSplit))))

IF LEN(@sItem) > 0

INSERT INTO @ReturnList SELECT Replace(@sItem,'''','')

END

IF LEN(@StringToSplit) > 0

INSERT INTO @ReturnList SELECT Replace(@StringToSplit,'''','')

RETURN

END

2. Now, you have several options available for using this UDF
1. In T-SQL -

select * from dbo.Func_SplitString('sujeet#kumar#Richmond#Virgina#23060#USA','#')

2. In the Stored Procedure -

CREATE Procedure Proc_AddEdit_GroupMaster

@GroupName VARCHAR (MAX)

AS

BEGIN

Declare @NewGroupName AS Varchar(MAX)

SET @NewGroupName = REPLACE(@GroupName,'"','''')

--Insert Return value from Func_SplitString to tblGroupMaster

Insert into tblGroupMaster

(GroupName) (Select DataItem From dbo.Func_SplitString(@NewGroupName,','))

END

Now, all you need to do is to determine the best way to use the UDF in your application.
Note: There are a few limitations with this solution. For homework, try to see if you can locate these. :)