This simple table-valued function, when combined with an APPLY operator, allows you to identify nth position strings within a string that has separators.
2019-10-19
5 reads
STRING_SPLIT with Index as Table-Valued Function
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
Related content
2019-11-01 (first published: 2019-10-19)
2,609 reads
The Best of SQLServerCentral, Vol.7
The seventh volume in this collection brings you a compilation of the best articles we've seen over the past year, from over 50 authors.
2022-04-26 (first published: 2013-03-15)
6,944 reads
Falling Over our Assumptions
There's a saying about what it says about a person to assume something. However it's something we all do every day. Phil Factor brings us a guest editorial about assumptions in SQL code.
2020-06-25 (first published: 2009-04-08)
676 reads
It Depends
It depends. The mantra of many DBAs and others in IT. Steve Jones reminds us why it applies.
2020-06-23 (first published: 2009-03-25)
415 reads