STRING_SPLIT with Index as Table-Valued Function

,

This simple table-valued function, when combined with an APPLY operator, allows you to identify nth position strings within a string that has separators.

CREATE FUNCTION [dbo].[tvfn_String_Split_with_Index]
(	
	@String varchar(max),
	@Separator varchar(10)
)
RETURNS @IndexedCharacters TABLE 
	(
		[Index]			int				NOT NULL,
		[Character]		varchar(max)	NULL
	)
AS
BEGIN 

	IF @String IS NULL

		INSERT INTO @IndexedCharacters
		SELECT 0 AS [Index], 'No value' AS [Character]

	ELSE 

		INSERT INTO @IndexedCharacters
		SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS [Index], value AS [Character]
		FROM STRING_SPLIT(@String, @Separator)

	RETURN

END

Rate

Share

Share

Rate