Parsing a Pairs of Values in a String While Preserving Order

Question

Parsing a Pairs of Values in a String While Preserving Order

wfw311

SSC Veteran

Points: 231
More actions
October 11, 2023 at 12:00 am

#4293839

Comments posted to this topic are about the item Parsing a Pairs of Values in a String While Preserving Order

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

P Jones SSChampion Points: 12353 More actions · Answer 1

I had to do this recently in SQL Server 2016 and used STRING_SPLIT which was far simpler and seems to keep the order anyway.

coryt Newbie Points: 3 More actions · Answer 2

Yea, this is NOT the way to accomplish this. String manipulation this way, especially in a recursive CTE is extremely expensive. string_split makes this easy even without the ordinal field added in 2022.

;WITH i AS (
   SELECT value = LTRIM(RTRIM(value)), ordinal = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
   FROM STRING_SPLIT(@OriginalString, ',')
)

SELECT field0 = MAX(IIF(i.ordinal%2 = 1, i.value, NULL))
   , field1 = MAX(IIF(i.ordinal%2 = 0, value, NULL))
FROM i
GROUP BY (i.ordinal+1)/2

coryt Newbie Points: 3 More actions · Answer 3

Yea, this is NOT the way to accomplish this. String manipulation this way, especially in a recursive CTE is extremely expensive. string_split makes this easy even without the ordinal field added in 2022.

;WITH i AS (
   SELECT value = LTRIM(RTRIM(value)), ordinal = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
   FROM STRING_SPLIT(@OriginalString, ',')
)

SELECT field0 = MAX(IIF(i.ordinal%2 = 1, i.value, NULL))
   , field1 = MAX(IIF(i.ordinal%2 = 0, value, NULL))
FROM i
GROUP BY (i.ordinal+1)/2

Ken McKelvey SSCoach Points: 18768 More actions · Answer 4

coryt wrote:

string_split makes this easy even without the ordinal field added in 2022.
SELECT value = LTRIM(RTRIM(value)), ordinal = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM STRING_SPLIT(@OriginalString, ',')

This may seem to work but once the string is split the data is relational and the order is not guaranteed. (A relation is an unordered set.)

Prior to SQL2022 you should use a string splitting UDF which gives the order of the strings. eg:

https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function

sgmunson SSC Guru Points: 110639 More actions · Answer 5

Yep... this is an EXTREMELY POOR WAY TO GO ABOUT IT... And for those folks not yet working with SQL Server 2022, Ken McKelvey's suggested link to Jeff Moden's DelimitedSplit TABLE-VALUED-FUNCTION is the right option to go with. I've been using that highly performant function since participating in testing it back in 2008.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)