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 comments on “How to split a string in SQL Server the right way
  1. 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.

1 Pings/Trackbacks for "How to split a string in SQL Server the right way"

Leave a Reply

Your email address will not be published. Required fields are marked *

*

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