I noticed the docs for STRING_SPLIT now show that there is a third parameter. This brings us an ordinal for the positioning of the data. The format is now:

STRING_SPLIT( string, separator [, enable_ordinal])

This is a bit or int that determines if a positional value is added to the result set. Here’s a quick demo.

No third parameter shows this:

2021-11-16 13_32_11-SQLQuery2.sql - (sjones (89))_ - Mic

If I add a 1 for the third parameter, I see this:

2021-11-16 13_32_37-SQLQuery2.sql - (sjones (89))_ - Mic

Right now this is only in Azure. I assume we’ll see this in SQL Server 2022, but we’ll have to wait and see.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

1 Response to STRING_SPLIT Has Ordinals

  1. JeffModen says:

    While I’m tickled that MS has finally come to their senses on this function, I’m also still ticked that MS had to come to their senses on this function. Either the research they did was woefully short and inadequate (something that seems to be a bit of a trademark for them when it comes to any new functionality… especially still missing functionality like the sequence generator that Erland Sommarskog requested well over a decade ago) or they just wanted to get it out on the street real bad… and succeeded in spades there.

    And then there are things like Index REBUILD and REORGANIZE. Lordy… have they never heard of Peter Norton? Do they really think that making a copy of all the data in a 2TB Clustered Index (or even a “tiny” 50GB index) is or ever has been the right way?

    MS gets away with murder just like “Big Blue” used to a long time ago… they’re the “only show” for a whole lot of people and lost touch with the people that have to use their stuff a very long time ago.

    It’s pretty disgusting from a “DevOps” standpoint, actually.


Comments are closed.