Split String Function

Question

Post reply

Split String Function

Christopher Kutsch

SSC Eights!

Points: 893
More actions
April 2, 2015 at 12:47 pm

#295636

Comments posted to this topic are about the item Split String Function

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

smiller 16120 SSC Enthusiast Points: 170 More actions · Answer 1

This is a nice function but I don't see any recursion, at least not as I understand it. What exactly do you mean?

Christopher Kutsch SSC Eights! Points: 893 More actions · Answer 2

The recursion comes in with the multiple executions of the SUBSTRING function. By using the Numbers table, or the tally table generated on the fly depending on your implementation, we are able to walk through the string, character by character, executing the SUBSTRING function along the way to find our delimiter and parse out the value we want to output to the caller. Recursion can be a source of hidden RBAR (Row By Agonizing Row), but tends to be lighter weight than a WHILE LOOP or CURSOR.

smiller 16120 SSC Enthusiast Points: 170 More actions · Answer 3

I see. I'm not sure I'd call that recursion, but it's a neat trick.

Here's another approach using a recursive CTE. I haven't coded it up as a function or catered for text qualified separators, but you get the idea:

DECLARE @String VARCHAR(255) = '1,2,345,6,yyr';

DECLARE @Separator VARCHAR(1)= ',';

WITH CTE

AS ( SELECT 1 AS PartNo ,

SUBSTRING(@String + @Separator, 1,

CHARINDEX(@Separator, @String + @Separator,

1) - 1) AS Part ,

SUBSTRING(@String,

CHARINDEX(@Separator, @String + @Separator,

1) + 1,

LEN(@String + @Separator)

- CHARINDEX(@Separator, @String + @Separator,

1)) AS Remainder

UNION ALL

SELECT PartNo + 1 AS PartNo ,

SUBSTRING(Remainder + @Separator, 1,

CHARINDEX(@Separator, Remainder + @Separator,

1) - 1) AS Part ,

SUBSTRING(Remainder,

CHARINDEX(@Separator, Remainder + @Separator,

1) + 1,

LEN(Remainder + @Separator)

- CHARINDEX(@Separator,

Remainder + @Separator, 1)) AS Remainder

FROM CTE

WHERE Remainder <> ''

)

SELECT PartNo ,

Part

FROM CTE

(Apologies, this was beautifully formatted before I c&ped from SSMS)

smiller 16120 SSC Enthusiast Points: 170 More actions · Answer 4

A bit of Googling around this topic led me to this article, which is pretty comprehensive:

http://sqlperformance.com/2012/07/t-sql-queries/split-strings

Christopher Kutsch SSC Eights! Points: 893 More actions · Answer 5

That is a great article. I hadn't read that before, but it definitely brings the performance of different approaches to light.

pazitzik Valued Member Points: 59 More actions · Answer 6

I recommend on this function(much better performance,Do it by XML query):

CREATE FUNCTION dbo.uf_SplitStringsToTable_XML

(

@List NVARCHAR(MAX),

@Delimiter NVARCHAR(255)

)

RETURNS TABLE

AS

RETURN

(

SELECT Data = y.i.value('(./text())[1]', 'INT')

FROM

(

SELECT x = CONVERT(XML, '<i>'

+ REPLACE(@List, @Delimiter, '</i><i>')

+ '</i>').query('.')

) AS a CROSS APPLY x.nodes('i') AS y(i)

);

Christopher Kutsch SSC Eights! Points: 893 More actions · Answer 7

Yes. That is better preforming as long as no XML reserved words are used.

https://technet.microsoft.com/en-us/library/ms145315%28v=sql.90%29.aspx

ksatpute123 Hall of Fame Points: 3325 More actions · Answer 8

-- Tally Table approach (No R-BAR optimal for millions of rows.)

CREATE FUNCTION udf_splitString

(

@STR NVARCHAR(MAX),

@sep NCHAR(1)

)

RETURNS TABLE

AS

RETURN(

WITH cteTally(POS)

AS

(

SELECT TOP (LEN(ISNULL(@sep + @STR + @sep, 0))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM SYS.COLUMNS a CROSS APPLY SYS.COLUMNS b

)

SELECT SUBSTRING(@sep + @STR + @sep, POS + 1, CHARINDEX(@sep, @sep + @STR + @sep, POS + 1) - POS -1) [Value]

FROM cteTally

WHERE POS <= LEN(@sep + @STR + @sep) - 1

AND SUBSTRING(@sep + @STR + @sep, POS, 1) = @sep

);

I think this a much cleaner and simple approach towards achieving the same result. Please test it on different sets and let me know if any changes are needed.

ksatpute123 Hall of Fame Points: 3325 More actions · Answer 9

Here are a few other approaches and there impacts:

-- Recursive CTE approach (Hidden R-BAR optimal for thousands to few hundred thousand rows.)

WITH cte_Split_String

AS

(

SELECT CAST(0 AS BIGINT) AS idx1,CHARINDEX(@sep,@str) idx2

UNION ALL

SELECT idx2 + 1,CHARINDEX(@sep,@str,idx2+1)

FROM cte_Split_String

WHERE idx2>0

)

INSERT INTO @value

SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) [value]

FROM cte_Split_String

OPTION (MAXRECURSION 0)

-- XML transform approach (Transform overhead and delimiter restrictions)

DECLARE @xml XML = (SELECT CONVERT(XML,'<r>' + REPLACE(@str,@sep,'</r><r>') + '</r>'))

INSERT INTO @value(Value)

SELECT t.value('.','NVARCHAR(MAX)')

FROM @xml.nodes('/r') AS x(t)

jonas.gunnarsson 52434 Ten Centuries Points: 1240 More actions · Answer 10

Also take a look at this article Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]

macintyre_bernie SSC Enthusiast Points: 122 More actions · Answer 11

Probably the best you will find out there: http://www.sqlservercentral.com/articles/Tally+Table/72993/

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 12

akljfhnlaflkj

SSC Guru

Points: 76202

March 8, 2016 at 4:45 pm

#1863860

Good function, thanks.

macintyre_bernie SSC Enthusiast Points: 122 More actions · Answer 13

I would suggest looking at Jeff Moden's function

Tally OH! An Improved SQL 8K “CSV Splitter” Function at http://www.sqlservercentral.com/articles/Tally+Table/72993/

AVX Old Hand Points: 391 More actions · Answer 14

unComplicate:

create table #tmp (x nvarchar(max))

declare @s-2 nvarchar(max) = 'a,1,2,x,3,4,z'

declare @aux nvarchar(max)

while charindex(',',@s) > 0

begin

set @aux = substring(@s,0,charindex(',',@s))

SET @s-2 = LTRIM(STUFF(@s,1,len(@aux)+1,''))

insert into #tmp select @aux

end

insert into #tmp select @s-2

select x from #tmp

drop table #tmp