Background
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.
Solution
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))
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 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 string
s as a tabular format, it's quite handy for situations like I explained above.
CodeProject