How to split a string in SQL Server the right way

I’ve seen many string splitting functions in SQL Server and many of them overwrite the string that is being split over and over which causes terrible performance by re-allocating memory every time. This is a terrible way to split a string in SQL Server.

Today I crafted a much more efficient SQL Server string splitting function. If you need varchar data, you can change the nvarchar to varchar with no problem.

I compared this against a table value parameter and it actually outperformed the table value parameter! The reason I believe, is because I have a very large string (about 10,000 items once it’s split) and that’s a LOT of transact SQL for SQL Server to parse.

 

13
Leave a Reply

avatar
2 Comment threads
12 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
2 Comment authors
Aaron Bertrandjjxtra Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
Notify of
trackback

[…] I cover a much better way to do a string split here: http://www.digitalruby.com/split-string-sql-server/ […]

Aaron Bertrand
Guest

Can you show the table-valued parameter, the code you used that did the same thing, and how exactly you measured the performance? This iterative approach has certainly not been anywhere near the top performers in my testing… so much so that I even left it out of my round-up two years ago.