Aaron Bertrand splits and cares about sort order:
Last year, I wrote about replacing all your CLR or custom string splitting functions with native calls to
STRING_SPLIT. As I work on a project migrating several Microsoft SQL Server instances to Linux, I am encountering one of the roadblocks I mentioned last time: the need to provide an element in the output to indicate the order of the elements in the input string. This meansSTRING_SPLITin its current form is out, because 1) it offers no such column; and, 2) the results are not guaranteed to be returned in any specific order. Are there other ways to achieve this functionality at scale and without CLR?
As Koen mentions in the comments, you can now get STRING_SPLIT with a sort parameter, but Aaron’s response is also valid: not everybody will have access to that today, so it still makes sense to understand the options.