使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?

拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?


当前回答

使用STRING_SPLIT的现代方法需要SQL Server 2016及以上版本。

DECLARE @string varchar(100) = 'Hello John Smith'

SELECT
    ROW_NUMBER() OVER (ORDER BY value) AS RowNr,
    value
FROM string_split(@string, ' ')

结果:

RowNr   value
1       Hello
2       John
3       Smith

现在可以从行号中得到第n个元素。

其他回答

你可以在SQL中拆分字符串,而不需要函数:

DECLARE @bla varchar(MAX)
SET @bla = 'BED40DFC-F468-46DD-8017-00EF2FA3E4A4,64B59FC5-3F4D-4B0E-9A48-01F3D4F220B0,A611A108-97CA-42F3-A2E1-057165339719,E72D95EA-578F-45FC-88E5-075F66FD726C'

-- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT 
    x.XmlCol.value('.', 'varchar(36)') AS val 
FROM 
(
    SELECT 
    CAST('<e>' + REPLACE(@bla, ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b 
CROSS APPLY b.RawXml.nodes('e') x(XmlCol);

如果需要支持任意字符串(带有xml特殊字符)

DECLARE @bla NVARCHAR(MAX)
SET @bla = '<html>unsafe & safe Utf8CharsDon''tGetEncoded ÄöÜ - "Conex"<html>,Barnes & Noble,abc,def,ghi'

-- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT 
    x.XmlCol.value('.', 'nvarchar(MAX)') AS val 
FROM 
(
    SELECT 
    CAST('<e>' + REPLACE((SELECT @bla FOR XML PATH('')), ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b 
CROSS APPLY b.RawXml.nodes('e') x(XmlCol); 

以下是我的解决方案,可能会对某些人有所帮助。修改以上Jonesinator的回答。

如果我有一个带分隔符的INT值字符串,并希望返回一个INT表(然后我可以加入)。如。44岁的1,3343 6,8765年

创建一个UDF:

IF OBJECT_ID(N'dbo.ufn_GetIntTableFromDelimitedList', N'TF') IS NOT NULL
    DROP FUNCTION dbo.[ufn_GetIntTableFromDelimitedList];
GO

CREATE FUNCTION dbo.[ufn_GetIntTableFromDelimitedList](@String NVARCHAR(MAX),                 @Delimiter CHAR(1))

RETURNS @table TABLE 
(
    Value INT NOT NULL
)
AS 
BEGIN
DECLARE @Pattern NVARCHAR(3)
SET @Pattern = '%' + @Delimiter + '%'
DECLARE @Value NVARCHAR(MAX)

WHILE LEN(@String) > 0
    BEGIN
        IF PATINDEX(@Pattern, @String) > 0
        BEGIN
            SET @Value = SUBSTRING(@String, 0, PATINDEX(@Pattern, @String))
            INSERT INTO @table (Value) VALUES (@Value)

            SET @String = SUBSTRING(@String, LEN(@Value + @Delimiter) + 1, LEN(@String))
        END
        ELSE
        BEGIN
            -- Just the one value.
            INSERT INTO @table (Value) VALUES (@String)
            RETURN
        END
    END

RETURN
END
GO

然后得到表格结果:

SELECT * FROM dbo.[ufn_GetIntTableFromDelimitedList]('1,20,3,343,44,6,8765', ',')

1
20
3
343
44
6
8765

在join语句中:

SELECT [ID], [FirstName]
FROM [User] u
JOIN dbo.[ufn_GetIntTableFromDelimitedList]('1,20,3,343,44,6,8765', ',') t ON u.[ID] = t.[Value]

1    Elvis
20   Karen
3    David
343  Simon
44   Raj
6    Mike
8765 Richard

如果你想返回一个nvarchar列表而不是int,那么只需更改表定义:

RETURNS @table TABLE 
(
    Value NVARCHAR(MAX) NOT NULL
)

我在网上寻找解决方案,下面的工作对我来说。 Ref。

然后像这样调用函数:

SELECT * FROM dbo.split('ram shyam hari gopal',' ')

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Split](@String VARCHAR(8000), @Delimiter CHAR(1))       
RETURNS @temptable TABLE (items VARCHAR(8000))       
AS       
BEGIN       
    DECLARE @idx INT       
    DECLARE @slice VARCHAR(8000)        
    SELECT @idx = 1       
    IF len(@String)<1 OR @String IS NULL  RETURN       
    WHILE @idx!= 0       
    BEGIN       
        SET @idx = charindex(@Delimiter,@String)       
        IF @idx!=0       
            SET @slice = LEFT(@String,@idx - 1)       
        ELSE       
            SET @slice = @String       
        IF(len(@slice)>0)  
            INSERT INTO @temptable(Items) VALUES(@slice)       
        SET @String = RIGHT(@String,len(@String) - @idx)       
        IF len(@String) = 0 break       
    END   
    RETURN       
END

使用SQL Server 2016及以上版本。使用这段代码修剪字符串,忽略NULL值,并按正确的顺序应用行索引。它也适用于空格分隔符:

DECLARE @STRING_VALUE NVARCHAR(MAX) = 'one, two,,three, four,     five'

SELECT ROW_NUMBER() OVER (ORDER BY R.[index]) [index], R.[value] FROM
(
    SELECT
        1 [index], NULLIF(TRIM([value]), '') [value] FROM STRING_SPLIT(@STRING_VALUE, ',') T
    WHERE
        NULLIF(TRIM([value]), '') IS NOT NULL
) R

这个问题不是关于字符串分割方法,而是关于如何获取第n个元素。

这里所有的答案都是使用递归,CTEs,多个CHARINDEX, REVERSE和PATINDEX,发明函数,调用CLR方法,数字表,CROSS APPLYs…大多数答案都包含了很多行代码。

但是-如果你真的只想要一个方法来获取第n个元素-这可以作为真正的一行程序来完成,没有UDF,甚至没有子选择…作为一个额外的好处:类型安全

用空格分隔第2部分:

DECLARE @input NVARCHAR(100)=N'part1 part2 part3';
SELECT CAST(N'<x>' + REPLACE(@input,N' ',N'</x><x>') + N'</x>' AS XML).value('/x[2]','nvarchar(max)')

当然,你可以使用变量作为分隔符和位置(使用sql:column直接从查询值中检索位置):

DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')

如果您的字符串可能包含禁止字符(特别是&><中的一个字符),您仍然可以这样做。只需首先在字符串上使用FOR XML PATH,隐式地用合适的转义序列替换所有禁止的字符。

另外,如果分隔符是分号,这是一种非常特殊的情况。在这种情况下,我首先将分隔符替换为“#DLMT#”,最后将其替换为XML标记:

SET @input=N'Some <, > and &;Other äöü@€;One more';
SET @dlmt=N';';
SELECT CAST(N'<x>' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');

更新SQL-Server 2016+

遗憾的是,开发人员忘记使用STRING_SPLIT返回部件的索引。但是,使用SQL-Server 2016+,有JSON_VALUE和OPENJSON。

使用JSON_VALUE,我们可以传入位置作为索引数组。

对于OPENJSON,文档清楚地说明:

当OPENJSON解析JSON数组时,该函数将JSON文本中元素的索引作为键返回。

像1,2,3这样的字符串只需要括号:[1,2,3]。 像这样的一串单词需要是["this","is","an","example"]。 这些都是非常简单的字符串操作。试试吧:

DECLARE @str VARCHAR(100)='Hello John Smith';
DECLARE @position INT = 2;

--We can build the json-path '$[1]' using CONCAT
SELECT JSON_VALUE('["' + REPLACE(@str,' ','","') + '"]',CONCAT('$[',@position-1,']'));

-位置安全的字符串分配器(从零开始):

SELECT  JsonArray.[key] AS [Position]
       ,JsonArray.[value] AS [Part]
FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray

在这篇文章中,我测试了各种方法,发现OPENJSON真的很快。甚至比著名的“delimitedSplit8k()”方法快得多……

更新2 -获取类型安全的值

只需使用double[[]],就可以在数组中使用数组。这允许使用类型化的with子句:

DECLARE  @SomeDelimitedString VARCHAR(100)='part1|1|20190920';

DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@SomeDelimitedString,'|','","'),'"]]');

SELECT @SomeDelimitedString          AS TheOriginal
      ,@JsonArray                    AS TransformedToJSON
      ,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(TheFirstFragment  VARCHAR(100) '$[0]'
    ,TheSecondFragment INT          '$[1]'
    ,TheThirdFragment  DATE         '$[2]') ValuesFromTheArray