Click here to Skip to main content
13,557,707 members
Click here to Skip to main content
Add your own
alternative version


3 bookmarked
Posted 3 May 2013
Licenced CPOL

TSQL Recursive Split Function

Rate this:
Please Sign up or sign in to vote.
TSQL Recursive Split function


In my last post, I showed how to split a string in TSQL. The 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 won't work. We need more effort to do so.


I am going to show how to split a string of any number of delimiters and we will get a recursive function to achieve that. The 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 than the first half and call the same function with it. This loop continues until we find that 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 initiates 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))
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
INSERT INTO @OldNames(Name) SELECT @SplitVal ;
INSERT INTO @OldNames(Name) select @RestOfTheStrVal;
INSERT INTO @OldNames(Name)
SELECT @SplitVal
SELECT Name FROM dbo.GetName(@RestOfTheStrVal,@delimeter)

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


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Shahriar Iqbal Chowdhury/Galib
Chief Technology Officer
Bangladesh Bangladesh
I am a Software Engineer and Microsoft .NET technology enthusiast. Professionally I worked on several business domains and on diverse platforms. I love to learn and share new .net technology and my experience I gather in my engineering career. You can find me from here

Personal Site
Personal Blog
FB MS enthusiasts group
About Me

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.180515.1 | Last Updated 3 May 2013
Article Copyright 2013 by Shahriar Iqbal Chowdhury/Galib
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid