Split SQL Function , Calculate string length b/w 2nd and 7 commas

Question

⁠⁠⁠⁠⁠Suppose that I have string having comma between them and I want to find the length of string between 2nd to 7th comma or (n to n+).

I am using this steps.

CREATE FUNCTION [dbo].[fn_split_1]
(
    @sInputList VARCHAR(MAX), -- List of delimited items
    @sDelimiter VARCHAR(5) = ',' -- Delimiter that separates items
) 
RETURNS @List TABLE (id int,item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
Declare @Count int 
SET @Count =1 
WHILE CHARINDEX(@sDelimiter, @sInputList, 0) <> 0
BEGIN
    SELECT
    @sItem = RTRIM(LTRIM(SUBSTRING(@sInputList, 1, CHARINDEX(@sDelimiter, @sInputList, 0) - 1))),
    @sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList, CHARINDEX(@sDelimiter, @sInputList, 0) + LEN(@sDelimiter), LEN(@sInputList))))
    IF LEN(@sItem) > 0
        INSERT INTO @List SELECT @Count ,@sItem
            SET @Count =@Count +1 
    END
    IF LEN(@sInputList) > 0
        INSERT INTO @List SELECT @Count ,@sInputList -- Put the last item in
  SET @Count =@Count +1 
    RETURN
END

Select sum(len(item))+(7-2)as'LengthOfChar(b/w 2 and 7 comma)','abc,def,efg,hij,lkm,nop,qrs,tuv' as'String'
from [fn_split_1]('abc,def,efg,hij,lkm,nop,qrs,tuv',',') where Id<7 and id>2

Input and Output Result

Inputs String is                 : 'abc,def,efg,hij,lkm,nop,qrs,tuv'
String between 2nd and 7th comma : 'efg,hij,lkm,nop,qrs'
LengthOfChar(b/w 2 and 7 comma)   : 19 
The result from the function  would be 
    ID      Values
    1       abc
    2       def
    3       efg
    4       hij
    5       lkm
    6       nop
    7       qrs
    8       tuv

But we cann't ignore the commas between the text. Is there more optimized ways to achieve this?

Answer 1

Bellow there is one solution based on XML & XQuery:

DECLARE @Source NVARCHAR(100) = N'abc,def,efg,hij,lkm,nop,qrs,tuv'
DECLARE @Start  INT = 2
DECLARE @End    INT = 7

-- Solution #1
SELECT
    (CONVERT(XML, N'<root><i>' + REPLACE(@Source, N',', N'</i><i>') +  N'</i></root>'))
    .query(N'for $t in (root/i[position() gt sql:variable("@Start") and position() le sql:variable("@End")]/text()) 
        return <len>{string-length($t)}</len>')
    .value('sum(len)', 'INT') + (@End - @Start - 1)

Demo

Edit 1: Replaced ...query('...').query('sum(len)').value('.', 'INT') with ...query('...').value('sum(len)', 'INT')

Note: The assumption is that source string doesn't contain XML reserved chars (ex. <). Let me know if this is your case.