I've mentioned that I love it when SQL Server gets new T-SQL functionality.
A useful function that was added in SQL Server 2016 was STRING_SPLIT. You can see it in action in the main image for this post.
It's another function that is great as far as it goes, but when I was a kid, this would be described as "nice try but no cigar".
It works, and it's fast, so what's missing. I think two things:
- You need to be able to know the position of each value retrieved from the string.
- You often need to trim the values as well.
So, what do we do if we need that, or if we aren't yet on SQL Server 2016 anyway?
We have a tool for that. The SDU Tool SplitDelimitedString does these things. It's implemented in boring T-SQL but it just works. I've seen attempts to do this via XML queries, and they are fast, but they also screw up with certain characters in the strings (like <, >, %, etc.). So I went for slow and reliable. Here's what it does:
The 3rd parameter indicates whether values should be trimmed or not. In this case, I asked for trimming.
You can see it in action here:
You can find out more about our free SDU Tools here: