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

Viewed 211

⁠⁠⁠⁠⁠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?

3 Answers
Related