Goldie Lesser (3/18/2016)
Ed Wagner (3/17/2016)
If it doesn't have a sequence number, I wont be using it. 'Nuff said.
I use Jeff's function all the time, and I can't even think of a single case where I've used the sequence number.
Can someone give a case of where the ordinal number is important?
One classic (simple) that I've had to use on many occasions is where someone passes two separate CSVs like the following (for example)...
A,B,C --Contents of the first parameter (@P1)
1,2,3 --Contents of the second parameter (@P2)
In this case, @P1 contains the entity identifiers (labels) for the values contained in @P2 and they have to be matched up and added to a table before use. Without an ordinal position value for each element, there is no ORDER BY and, therefor, no guarantee that SQL Server would Match A with 1, B with 2, and C with 3 without it.
A much more sophisticated (complex) example would be in the form of a poor-man's full text search where a word must exist, say, somewhere between word #6 and word #10 and a different word must be within 3 words of the first word (proximity search).
There's also the example (medium complexity) of string cleansing where a string is ripped apart at the character level and must reassembled in the correct order not including the characters that have been omitted.
I've needed it for all sorts of encode/decode functionality, all sorts of different string manipulation, and I've also used it for validation of the order of CSVs. For example, checking that the an "EAV-like" parameter with 20 sets of 3 elements each has the first 2 elements of each set contains only numeric data while the third contains only varchar and then use those same ordinal element numbers as the driver for a CROSSTAB to correct pivot the information into a 3 column result set.
Tons and tons of uses for me. I probably couldn't name all of the different things I've done with the ordinal position value.