Was playing with STRING_SPLIT function the other day in my Azure SQL database instance and happened to notice a small enhancement which seems to have appeared.
The STRING_SPLIT function now includes a new optional parameter which can be used for returning an ordinal number as a part of the resultset.
This comes handy in cases where we require ordering the result so as to do some kind of further manipulations like returning TOP N entries.
The default usage of STRING_SPLIT is as given below
Now lets see the new usage. This just needs third argument to be passed as bit value (0 or 1 0 being default) based on whether you need ordinal to be returned or not
So the modified query will look like
The intellisense doesnt seem to recognize the new argument yet probably because the roll out has not been official yet
I have also learned from some of my friends that not every region has this change rolled out currently so the official announcement might come only after the feature has been rolled out across
So new syntax for STRING_SPLIT can be given is
STRING_SPLIT ( string , separator, [ordinal])
