Split SQL function, calculate b / w string length 2 and 7 commas
Suppose I have a string with a semicolon in between and I want to find the length of a string between the 2nd or 7th comma or (n to n +).
I am using these 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
Result of input and output
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 cannot ignore the commas between the text. Are there more optimized ways to achieve this?
source to share
Following is one XML and XQuery based solution:
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)
Edit 1: Replace ...query('...').query('sum(len)').value('.', 'INT')
with...query('...').value('sum(len)', 'INT')
Note. It is assumed that the original string does not contain any reserved XML characters (for example <
). Let me know if this is your case.
source to share
It hurts me at first to see the loops in the split / parse functions.
However, this alternative will also return an ordinal
Example
Declare table @YourTable (String varchar (max)) Insert into @YourTable values ('A, Protect, EFG, Hij, LKM, NOP, QRS, TUV)
Select A.*
,B.*
From @YourTable A
Cross Apply (
Select Value = sum(len(RetVal)+1)-1
From [dbo].[udf-Str-Parse-8K](A.String,',')
Where RetSeq between 2 and (7-1)
) B
Returns
String Value
abc,def,efg,hij,lkm,nop,qrs,tuv 19
UDF if interested
CREATE FUNCTION [dbo].[udf-Str-Parse-8K] (@String varchar(max),@Delimiter varchar(25))
Returns Table
As
Return (
with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ),
cte3(N) As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)
Select RetSeq = Row_Number() over (Order By A.N)
,RetVal = LTrim(RTrim(Substring(@String, A.N, A.L)))
From cte4 A
);
--Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')
Just for fun
Select * From [dbo].[udf-Str-Parse-8K]('abc,def,efg,hij,lkm,nop,qrs,tuv',',')
Returns
RetSeq RetVal
1 abc
2 def
3 efg
4 hij
5 lkm
6 nop
7 qrs
8 tuv
source to share
Do you need to split? You can find the position of each "Nth" symbol and get the difference.
CREATE FUNCTION dbo.NthCharIndex
(
@CharToFind varchar(8000),
@StringToSearch varchar(8000),
@N int
)
RETURNS int
AS
BEGIN
DECLARE @pos int, @ctr int, @ret int
SET @pos = 0
SET @ctr = 0
BEGIN
WHILE(@ctr < @N)
BEGIN
SELECT @ret = CHARINDEX(@CharToFind, @StringToSearch, @pos + 1)
SET @ctr = @ctr + 1
SET @pos = @ret
END
END
RETURN(@ret)
END
GO
Then you just need to find the difference.
Select dbo.nthCharIndex('a', 'bananaBanana', 6) - dbo.nthCharIndex('a', 'bananaBanana', 1)
source to share