Technical Article

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]intNOT 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