拆分SQL功能，计算字符串长度B/W 2nd和7逗号

Question

suppose我在它们之间具有逗号的字符串，我想在第二到第7逗号之间找到字符串的长度，或者(n to n to n (。

我正在使用此步骤。

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

输入和输出结果

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

，但我们不能忽略文本之间的逗号。是否有更多优化的方法来实现这一目标？

Answer 1

bellow有一个基于xml＆amp;的解决方案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)

演示

编辑1：用...query('...').query('sum(len)').value('.', 'INT')替换CC_1

注意：假设是源字符串不包含XML保留的字符(例如<(。让我知道这是否是您的情况。

Answer 2

首先，我会在拆分/解析功能中看到循环。

也就是说，此替代方案还将返回序列号

示例

声明 @yourtable表(字符串varchar(max((插入 @yourtable值('ABC，DEF，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

返回

String                           Value
abc,def,efg,hij,lkm,nop,qrs,tuv  19    

如果有兴趣

UDF

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','||')

只是为了娱乐

Select * From [dbo].[udf-Str-Parse-8K]('abc,def,efg,hij,lkm,nop,qrs,tuv',',')

返回

RetSeq  RetVal
1       abc
2       def
3       efg
4       hij
5       lkm
6       nop
7       qrs
8       tuv

Answer 3

您需要拆分吗？您可以找到每个" nth"字符的位置并获得区别。

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

然后您只需要找到差异。

Select  dbo.nthCharIndex('a', 'bananaBanana', 6) - dbo.nthCharIndex('a', 'bananaBanana', 1)