如何拆分分隔字符串以便访问单个项目？

Question

使用 SQL Server，如何拆分字符串以便可以访问项目 x？

取一个字符串“Hello John Smith”。如何按空格拆分字符串并访问索引 1 处应返回“John”的项目？

原文由 GateKiller 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

您可能会在 _SQL User Defined Function to Parse a Delimited String_ 中找到有用的解决方案（来自 The Code Project ）。

您可以使用这个简单的逻辑：

 Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null

WHILE LEN(@products) > 0
BEGIN
    IF PATINDEX('%|%', @products) > 0
    BEGIN
        SET @individual = SUBSTRING(@products,
                                    0,
                                    PATINDEX('%|%', @products))
        SELECT @individual

        SET @products = SUBSTRING(@products,
                                  LEN(@individual + '|') + 1,
                                  LEN(@products))
    END
    ELSE
    BEGIN
        SET @individual = @products
        SET @products = NULL
        SELECT @individual
    END
END

原文由 Jonesinator 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

@Aaron Bertrand 的修改功能

CREATE FUNCTION [dbo].[SplitString]
(
    @List NVARCHAR(MAX),
    @Delim VARCHAR(255),
    @Idx int
)
RETURNS NVARCHAR(1000)
AS
BEGIN
    DECLARE @ValueTable TABLE(String NVARCHAR(50), Ind int)
    DECLARE @Value NVARCHAR(50)
    BEGIN
    INSERT INTO @ValueTable
    SELECT Value, idx FROM
        (SELECT [Value], idx = RANK() OVER (ORDER BY n) FROM
              (
                SELECT n = Number,
                [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
                CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
                FROM
                        (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
                         FROM sys.all_objects) AS x
                WHERE Number <= LEN(@List)
                AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
              ) AS y
          ) AS R WHERE idx = @Idx
    SET @Value = (SELECT String FROM @ValueTable)
    END
    RETURN @Value
END
GO

原文由 Josef B. 发布，翻译遵循 CC BY-SA 4.0 许可协议