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 Table
or 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. ;)
String.Split()
call is assumed to be determinsitic, why is there any difference for a similar string manipulation function in SQL)?