TSQL Recursive Split Function

Background

In my last post I showed how to split a string in TSQL. Problem with that splitting is it can split a string if resultant split count is <=2, i.e. it can split only string like “a,b” or “first,last” or “java,c”. But while doing the parsing of  string of whom resultant split count is >2, it wont work. We need more effort to do so.

Solution

I am gonna show how to split a string of any number of delimiter and we will recursive function to achieve that. Idea is very simple,  Create a function that will take the input string and get the first index of delimiter and return the string up to first index and insert that first half into a table. Next is find the rest of the string other then first half and call the same function with it. This loop continues until we find there is no delimiter left in string to do further recursive call, so we return the original caller function and all the way to parent function who initiate the recursive call.

Implementation of the above idea is the function illustrated below,

CREATE FUNCTION [GetName] (@StrVal nvarchar(MAX),@delimeter nvarchar(1))
RETURNS @OldNames TABLE (Name varchar(MAX))
AS
BEGIN
DECLARE @SplitVal nvarchar(MAX);
DECLARE @RestOfTheStrVal nvarchar(MAX);
SET @SplitVal=LTRIM(SUBSTRING(@StrVal, 0,CHARINDEX(@delimeter,@StrVal)));
SET @RestOfTheStrVal=LTRIM(SUBSTRING(@StrVal, CHARINDEX(@delimeter,@StrVal)+1,len(@StrVal)));

IF CHARINDEX(@delimeter,@RestOfTheStrVal)<=0
BEGIN
IF @SplitVal IS NOT NULL
INSERT INTO @OldNames(Name) SELECT @SplitVal ;
IF @SplitVal IS NOT NULL
INSERT INTO @OldNames(Name) select @RestOfTheStrVal;
return;
END
INSERT INTO @OldNames(Name)
SELECT @SplitVal
UNION
SELECT Name FROM dbo.GetName(@RestOfTheStrVal,@delimeter)
RETURN
END

And this time  let me remind you this solution is applicable  if you have a string with a single delimiter (means you can use only one delimiter). Though its returns resultant strings as a tabular format, its quite handy for situation like I explained above.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s