Splitting long strings in SQL

Question

The inline solution suggested here: How do I split a string so I can access item x? by vzczc only works for strings containing less than 100 elements because of the limit of recursion depth (and you can't change the limit inside a UDF).

Any other ways of splitting long strings with good performance? The UDF I've been using (not inline) has been very slow:

CREATE FUNCTION [dbo].[fn_ParseDelimitedStrings](@String nvarchar(MAX), @Delimiter char(1))
RETURNS @Values TABLE
(
 RowId int Not Null IDENTITY(1,1) PRIMARY KEY
,Value nvarchar(255) Not Null
)
AS
BEGIN
    DECLARE  @startPos smallint
        ,@endPos smallint

    IF (@String = '')
    RETURN

    IF (RIGHT(@String, 1) != @Delimiter)
        SET @String = @String + @Delimiter
    SET @startPos = 1
    SET @endPos = CharIndex(@Delimiter, @String)
    WHILE @endPos > 0
    BEGIN
        INSERT @Values(Value)
        SELECT LTRIM(RTRIM(SUBSTRING(@String, @startPos, @endPos - @startPos)))
        -- remove the delimiter just used
        SET @String = STUFF(@String, @endPos, 1, '')
        -- move string pointer to next delimiter
        SET @startPos = @endPos
        SET @endPos = CHARINDEX(@Delimiter, @String)
    END
    RETURN
END

There is an awful number of questions/answers addressing this issue. Have you tried [this solution using XML](http://stackoverflow.com/a/2837662/1297603). — Yaroslav, Nov 07 '12 at 16:01

score 1 · Answer 1 · answered Nov 07 '12 at 16:05

Not sure if this will give you same performance as your own example (probably will), but try one I'm using:

 CREATE FUNCTION [dbo].[UDF_Split]
 (
     @InputParam VARCHAR(4000),
     @delimiter CHAR(1)
 ) 
 RETURNS @tSplit TABLE (
 ItemValue VARCHAR(255)
 ) 
 AS

 BEGIN

-- Declares --
--------------

-- Variables.
DECLARE @ItemValue VARCHAR(255)


-- Initialize --
----------------

-- Make sure it has a trailing comma.
IF RIGHT(@InputParam, 1) <> @delimiter SELECT @InputParam = @InputParam + @delimiter 


-- Put values separated by delimiter in a string to a table as records.
WHILE CHARINDEX(@delimiter, @InputParam) <> 0
BEGIN 
    SELECT @ItemValue = CONVERT(VARCHAR(255), SUBSTRING(@InputParam, 1, CHARINDEX(@delimiter , @InputParam) -1))


    if DATALENGTH(@ItemValue) = 0
        SELECT @InputParam = SUBSTRING(@InputParam,2, len(@InputParam) - 1)
    else
    begin
        SELECT @InputParam = SUBSTRING(@InputParam, DATALENGTH(RTRIM(@InputParam)) - ((DATALENGTH(RTRIM(@InputParam)) - DATALENGTH(RTRIM(@ItemValue))) - 2), 4000) 

        INSERT  @tSplit 
        SELECT  CONVERT(VARCHAR(255), @ItemValue)
    end     


END 

-- Return --
------------
RETURN
END

score 1 · Accepted Answer · edited May 23 '17 at 12:04

1

You can use the MAXRECURSION hint when calling your function to eliminate the problem of only being able to split strings with less than 100 elements, so using the example in the link you have used you can call:

-- CREATE A STRING TO SPLIT WITH OVER 100 DELIMITERS
DECLARE @s VARCHAR(1000) = 'a'

SELECT  @s = @s + ',a'
FROM    master..spt_values
WHERE   type= 'p'
AND     number BETWEEN 1 AND 150

--SPLIT LONG STRING
SELECT  *
FROM    dbo.splitString(@s, ',')
OPTION (MAXRECURSION 0) -- NO LIMIT

edited May 23 '17 at 12:04

Community

1
1

answered Nov 07 '12 at 16:42

GarethD

68,045
10
83
123

1

In the end we went with this solution: http://stackoverflow.com/a/240586/572769 it's fast and supports strings longer than 4000 characters. – Pking Dec 06 '12 at 13:03

Splitting long strings in SQL

2 Answers2