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. ;)
The sort order of the output rows matches the order of the substrings in the input string.
(here is the commit).String.Split()
call is assumed to be determinsitic, why is there any difference for a similar string manipulation function in SQL)?