I need to split a comma delimited string, manipulate it, and then concatenate it back into a single string retaining the original order of the data (if possible).
For example, take a column definition list of a
CREATE TABLE statement (as a string) like so
'BrentOzarColumn INTEGER, PaulWhiteColumn DATETIME, ErikDarlingColumn VARCHAR(100)'. I'd like to split the comma delimited list to a result set, such as using SQL Server's built in function
STRING_SPLIT() like so:
SELECT TRIM([Value]) AS CoolDataPeople FROM STRING_SPLIT('BrentOzarColumn INTEGER, PaulWhiteColumn DATETIME, ErikDarlingColumn VARCHAR(100)', ',').
Without specifying an
ORDER BY clause, this repeatedly yields (by coincedence?) the following results that appear to be ordered by their same ordering as they are in the string:
Once I have the result set above, I want to apply some additional string manipulations to each row (such as append some constant text), and then concatenate every row back with a function like
STRING_AGG() (goodbye days of
STUFF ... FOR XML PATH :) in the same order as the original string. So an example of my final result could be
'BrentOzarColumn INTEGER SQLROX, PaulWhiteColumn DATETIME SQLROX, ErikDarlingColumn VARCHAR(100) SQLROX'.
Ultimately my question is: Are the results of the
STRING_SPLIT() function returned in a deterministic order? I know without an
ORDER BY clause, ordering is not guaranteed when selecting from a dataset like a
View, but was wondering if there's a difference with functions?
As I type this out, I have a hunch the answer is no, the ordering is not deterministic therefor I'm not guaranteed the order of the results. Furthermore, I'm betting there's possible additional nondeterminism added for each function I run on top of the results, especially when I combine them back together with
STRING_AGG(). (Regardless of the answer, I appreciate your help, and you're all cool data people. ;)