如何通过索引从分隔字符串获取元素？

问题描述：

我有这个功能（来源：searchsqlserver）：如何通过索引从分隔字符串获取元素？

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000)    -- List of delimited items 
    , @sDelimiter VARCHAR(8000) = ','  -- delimiter that separates items 
) RETURNS @List TABLE (item VARCHAR(8000)) 
    BEGIN 
    DECLARE @sItem VARCHAR(8000) 
    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 @sItem 
     END 

    IF LEN(@sInputList)> 0 
    INSERT INTO @List SELECT @sInputList -- Put the last item in 
    RETURN 
    END 
GO

它作为参数字符串和分隔符，并返回一个带分隔符的元素之一。

select * from fnSplit('1,22,333', ',') -- returns 1 22 333

我会承认，我对SQL很陌生，我根本无法遵循这个函数背后的整个逻辑。我试图实现的是一个函数，它具有第三个参数（一个索引），并返回索引提及的位置上的元素。例如：

select * from fnSplit('1 22 333 444 5555 666', ' ' , 2) -- 333 

select * from fnSplit('1 22 333 444 5555 666', ' ' , 0) -- 1

如果您不熟悉SQL，请先了解如何正确存储值。分隔字符串不是存储值列表的SQLish方式。 –

'我根本无法遵循这个函数背后的整个逻辑'使用多态表值函数来解析字符串不是很有效。无论如何http://rextester.com/RDU95829 – lad2025

要存储值的列表，我会使用表/ #temp table/@table变量（'DECLARE @array TABLE（IntValue INT）; INSERT @array（IntValue）VALUES（10 ），（20）;'）或XML（例如：'DECLARE @x XML ='''） –

答

使用与multistatment表值UDF循环来解析字符串是非常低效的。更好的方法：Split strings the right way – or the next best way

无论如何，如果你要调整你的功能，你可以设置表变量IDENTITY列，然后筛选基于第三个参数：

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000)    -- List of delimited items 
    , @sDelimiter VARCHAR(8000) = ','  -- delimiter that separates items 
    ,@num INT 
) RETURNS @List TABLE (item VARCHAR(8000)) 
    BEGIN 
    DECLARE @ListHelper AS TABLE(id INT IDENTITY(1,1), item VARCHAR(8000)); 
    DECLARE @sItem VARCHAR(8000) 
    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 @ListHelper SELECT @sItem 
     END 

    IF LEN(@sInputList)> 0 
    INSERT INTO @ListHelper SELECT @sInputList -- Put the last item in 
    INSERT INTO @List 
    SELECT item 
    FROM @ListHelper 
    WHERE id = @num 
    RETURN 
    END 
GO 

select * from fnSplit('1 22 333 444 5555 666', ' ' , 3); 
--333

LiveDemo

答

首先，循环拆分效率不高。

与索引筛选实施例

Select * 
From [dbo].[udf-Str-Parse-8K]('1 22 333 444 5555 666', ' ' ) 
Where RetSeq=3

RetSeq RetVal 
3  333

实施例不具有索引筛选

Select * 
From [dbo].[udf-Str-Parse-8K]('1 22 333 444 5555 666', ' ' )

RetSeq RetVal 
1  1 
2  22 
3  333 
4  444 
5  5555 
6  666

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

答

你可以改变你分割字符串函数，包括行号：

CREATE FUNCTION dbo.fnSplit2(
    @sInputList VARCHAR(8000)    -- List of delimited items 
    , @sDelimiter VARCHAR(8000) = ','  -- delimiter that separates items 
) RETURNS @List TABLE (item VARCHAR(8000)) 
    BEGIN 
    DECLARE @sItem VARCHAR(8000), 
      @RowNumber int 

    set @RowNumber = 0 

    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 @sItem, @RowNumber 

      set @RowNumber = @RowNumber + 1 
     END 

    set @RowNumber = @RowNumber + 1 

    IF LEN(@sInputList)> 0 
    INSERT INTO @List SELECT @sInputList, @RowNumber -- Put the last item in 
    RETURN 
    END

如何通过索引从分隔字符串获取元素？

相关推荐