Splitting delimited strings with undefined number of delimiters

Question

I have a problem, I have the following line of strings

0009 - The Good Boy Song
0003 - Alphabet Song
0008 - Flame-thrower Guide

I have a split function that currently takes two parameters,

ALTER FUNCTION [dbo].[Split]
(
 @String NVARCHAR(4000),
 @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(

WITH Split(stpos,endpos) 
AS(
    SELECT 0 AS stpos, CHARINDEX(@Delimiter2,@String) AS endpos
    UNION ALL
    SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
        FROM Split
        WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
    'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)

I need to make sure that the ouput is something like

Id       Data
0009 The Good Boy Song
0003 Alphabet Song
0008 Flame-thrower Guide

and not something like

0009 The Good Boy Song
0003 Alphabet Song
0008 Flame
thrower Guide

I'm using this on SSRS, where in I'm sending a multiple valued argument, it looks like this as SSRS sends multiple values in CSV form.

'0009 - The Good Boy Song,0003 - Alphabet Song,0008 - Flame-thrower Guide'

How I update my function to handle this scenario?

score 0 · Accepted Answer · edited May 23 '17 at 12:18

0

I've updated your function slightly:

ALTER FUNCTION [Split]
(
 @String NVARCHAR(4000),
 @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(

WITH Split(stpos,endpos) 
AS(
    SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
    UNION ALL
    SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
        FROM Split
        WHERE endpos > 0
)
, SplitData AS
(
  SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
      'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
  FROM Split
)
SELECT ID
  , NumericData = SUBSTRING(Data, 1, 4)
  , TextData = SUBSTRING
    (
      Data
      , 8
      , len(Data) - 7
    )
FROM SplitData
)

This is giving OK results for me.

See SQL Fiddle with demo.

Edit after comment:

To prevent issues with the recursion limit you need to set MAXRECURSION outside of the function, i.e. when you're calling it with a SELECT statement:

SELECT *
FROM dbo.Split
(
  N'0009 - The Good Boy Song,0003 - Alphabet Song,0008 - Flame-thrower Guide'
  , N','
)
OPTION (MAXRECURSION 0)

You cannot apply the hint in the function as it's written.

See this SO question and this MSDN discussion for further details and other potential workarounds.

One more comment... If you are expecting long strings, maybe you should consider changing the parameter from NVARCHAR(4000) to NVARCHAR(max)?

edited May 23 '17 at 12:18

Community

1
1

answered May 27 '13 at 10:50

Ian Preston

38,816
8
95
92

As per the question, the string is coming from an SSRS multi-valued parameter, which is comma delimited. If the OP confirms they need your specific case I can update the answer accordingly. – Ian Preston May 27 '13 at 11:01
Just the way I was thinking... Thank you. – mirageservo May 28 '13 at 09:24
If I supply around more than 100 of those values, I keep getting recursion error, I can't add up OPTION(MAXRECURSION N) why? – mirageservo May 28 '13 at 11:39
I've applied some more detail to help with your recursion issue. – Ian Preston May 28 '13 at 12:01
Hi Ian, this is perfect, that was I was doing before adding the option hint inside the function, now I understand, thanks for pointing it out. – mirageservo May 29 '13 at 02:35
You're welcome! If you find an answer helpful, please consider upvoting and/or setting it as the accepted answer. – Ian Preston May 29 '13 at 09:35

Splitting delimited strings with undefined number of delimiters

1 Answers1