SQL Server 2016+ New STRING_SPLIT Function - Lack of Row Number - PLEASE VOTE!

For all questions relating to hardware and software
Post Reply
User avatar
robinwilson16
Site Admin
Posts: 184
Joined: Sun Oct 16, 2016 6:46 pm
Forename: Robin
Surname: Wilson
College Name: FEA
Position: MIS Consultant
LinkedIn Profile: https://www.linkedin.com/in/robinwilson16

SQL Server 2016+ New STRING_SPLIT Function - Lack of Row Number - PLEASE VOTE!

Post by robinwilson16 » Wed Mar 22, 2017 11:54 pm

SQL Server has a new STRING_SPLIT function that makes it much easier to split a string into multiple rows of data.
However the output does not contain a row number meaning that if you need to use windowing functions such as a running total you still need to revert to more traditional methods to split the data or alternatively insert the data into a table with an identity column.

If you believe Microsoft should add this option please vote for my Microsoft Connect request here (or is there a better workaround I have missed?):
https://connect.microsoft.com/SQLServer ... row-number

This code helps to highlight the issue. In trying to apply a running total the results have to be ordered and the position of Susan and Michael is switched. Had a Row Number field existed, ordering by that would have maintained the original order:

Code: Select all

DECLARE @str NVARCHAR(MAX) = 'Ben|10, Bob|325, Susan|7, Michael|99'

SELECT
	N.PersonName,
	N.Amount,
	RunningTotal = SUM ( N.Amount ) OVER ( ORDER BY N.PersonName ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
FROM (
	SELECT
		PersonName = LEFT ( TRIM ( Value ), CHARINDEX ( '|', TRIM ( Value ) ) - 1 ),
		Amount = CAST ( RIGHT ( TRIM ( Value ), CHARINDEX ( '|', REVERSE ( TRIM ( Value ) ) ) - 1 ) AS int )
	FROM STRING_SPLIT ( @str, ',' )
) N
Here is a workaround to this problem using a CTE where Susan and Michael remain in the correct order but this is much longer and more complicated:

Code: Select all

DECLARE @str NVARCHAR(MAX) = 'Ben|10, Bob|325, Susan|7, Michael|99'

;WITH N AS (
	SELECT
		RowNumber = 1,
		StartPos = CAST ( 1 AS BIGINT ),
		EndPos = CHARINDEX ( ', ', @str ) - 1,
		PersonName = LEFT ( SUBSTRING ( @str, 1, CHARINDEX ( ', ', @str ) - 1 ), CHARINDEX ( '|', SUBSTRING ( @str, 1, CHARINDEX ( ', ', @str ) - 1 ) ) - 1 ),
		Amount = CAST ( RIGHT ( SUBSTRING ( @str, 1, CHARINDEX ( ', ', @str ) - 1 ), CHARINDEX ( '|', REVERSE ( SUBSTRING ( @str, 1, CHARINDEX ( ', ', @str ) - 1 ) ) ) - 1 ) AS int )
	UNION ALL

	SELECT
		RowNumber = RowNumber + 1,
		StartPos = 
			StartPos 
			+ CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2,
		EndPos = 
			CASE
				WHEN CHARINDEX ( ', ', SUBSTRING ( @str, StartPos + CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, LEN ( @str ) ) ) > 0 THEN -- Not last record as found another ,
					StartPos
					+ CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 1
					+ CHARINDEX ( ', ', SUBSTRING ( @str, StartPos + CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, LEN ( @str ) ) ) - 1
				ELSE
					LEN ( @str )
			END,
		PersonName =
			LEFT ( 
				CASE
					WHEN CHARINDEX ( ', ', SUBSTRING ( @str, StartPos + CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, LEN ( @str ) ) ) > 0 THEN -- Not last record as found another ,
						SUBSTRING ( 
							@str, 
							StartPos 
							+ CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, 
							CHARINDEX ( ', ', SUBSTRING ( @str, StartPos + CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, LEN ( @str ) ) ) - 1
						)
					ELSE
						SUBSTRING ( 
							@str, 
							StartPos 
							+ CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, 
							LEN ( @str ) -
							StartPos 
							+ CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2
						)
				END,
				CHARINDEX ( 
					'|', 
					CASE
						WHEN CHARINDEX ( ', ', SUBSTRING ( @str, StartPos + CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, LEN ( @str ) ) ) > 0 THEN -- Not last record as found another ,
							SUBSTRING ( 
								@str, 
								StartPos 
								+ CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, 
								CHARINDEX ( ', ', SUBSTRING ( @str, StartPos + CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, LEN ( @str ) ) ) - 1
							)
						ELSE
							SUBSTRING ( 
								@str, 
								StartPos 
								+ CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, 
								LEN ( @str ) -
								StartPos 
								+ CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2
							)
					END
				) - 1
			),
		Amount = 
			CAST ( 
				RIGHT ( 
					CASE
						WHEN CHARINDEX ( ', ', SUBSTRING ( @str, StartPos + CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, LEN ( @str ) ) ) > 0 THEN -- Not last record as found another ,
							SUBSTRING ( 
								@str, 
								StartPos 
								+ CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, 
								CHARINDEX ( ', ', SUBSTRING ( @str, StartPos + CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, LEN ( @str ) ) ) - 1
							)
						ELSE
							SUBSTRING ( 
								@str, 
								StartPos 
								+ CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, 
								LEN ( @str ) -
								StartPos 
								+ CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2
							)
					END,
					CHARINDEX ( 
						'|', 
						REVERSE ( 
							CASE
								WHEN CHARINDEX ( ', ', SUBSTRING ( @str, StartPos + CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, LEN ( @str ) ) ) > 0 THEN -- Not last record as found another ,
									SUBSTRING ( 
										@str, 
										StartPos 
										+ CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, 
										CHARINDEX ( ', ', SUBSTRING ( @str, StartPos + CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, LEN ( @str ) ) ) - 1
									)
								ELSE
									SUBSTRING ( 
										@str, 
										StartPos 
										+ CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2, 
										LEN ( @str ) -
										StartPos 
										+ CHARINDEX ( ', ', RIGHT ( @str, LEN ( @str ) - StartPos ) ) + 2
									)
							END
						)
					) - 1
				)
			AS int )
	FROM N
	WHERE
		EndPos < LEN ( @str )
)
SELECT
	PersonName,
	Amount,
	RunningTotal = SUM ( N.Amount ) OVER ( ORDER BY N.RowNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
FROM N

Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests