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?