使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
我使用弗雷德里克的答案,但这在SQL Server 2005中不起作用
我修改了它,我使用select with union all,它可以工作
DECLARE @str varchar(max)
SET @str = 'Hello John Smith how are you'
DECLARE @separator varchar(max)
SET @separator = ' '
DECLARE @Splited table(id int IDENTITY(1,1), item varchar(max))
SET @str = REPLACE(@str, @separator, ''' UNION ALL SELECT ''')
SET @str = ' SELECT ''' + @str + ''' '
INSERT INTO @Splited
EXEC(@str)
SELECT * FROM @Splited
结果集是:
id item
1 Hello
2 John
3 Smith
4 how
5 are
6 you
其他回答
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
并使用它
select *from dbo.fnSplitString('Querying SQL Server','')
这里的大多数解决方案使用while循环或递归cte。我保证,如果你可以使用空格以外的分隔符,基于集合的方法会更好:
CREATE FUNCTION [dbo].[SplitString]
(
@List NVARCHAR(MAX),
@Delim VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( 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
);
示例用法:
SELECT Value FROM dbo.SplitString('foo,bar,blat,foo,splunge',',')
WHERE idx = 3;
结果:
----
blat
您还可以将需要的idx作为参数添加到函数中,但我将把它作为练习留给读者。
您不能仅使用SQL Server 2016中添加的本地STRING_SPLIT函数来实现这一点,因为不能保证输出将按照原始列表的顺序呈现。换句话说,如果你传递3,6,1结果可能是这个顺序,但它可能是1,3,6。我已经在这里请求社区的帮助来改进内置功能:
请帮助改进STRING_SPLIT
有了足够的定性反馈,他们可能会考虑做出以下改进:
STRING_SPLIT不是特性完整的
更多关于拆分函数,为什么(和证明)while循环和递归cte不能扩展,以及更好的替代方案,如果拆分字符串来自应用层:
以正确的方式拆分字符串-或者退而求其次的方式 拆分字符串:后续 分割字符串:现在使用更少的T-SQL 比较字符串分割/连接方法 处理一个整数列表:我的方法 拆分整数列表:另一个汇总 更多关于拆分列表的内容:自定义分隔符、防止重复和维护顺序 在SQL Server中删除字符串中的重复项
在SQL Server 2016或更高版本上,你应该看看STRING_SPLIT()和STRING_AGG():
性能惊喜和假设:STRING_SPLIT() STRING_SPLIT()在SQL Server 2016:后续#1 STRING_SPLIT()在SQL Server 2016:后续#2 SQL Server v.Next: STRING_AGG()性能 使用SQL Server的新STRING_AGG和STRING_SPLIT函数解决老问题
CREATE TABLE test(
id int,
adress varchar(100)
);
INSERT INTO test VALUES(1, 'Ludovic Aubert, 42 rue de la Victoire, 75009, Paris, France'),(2, 'Jose Garcia, 1 Calle de la Victoria, 56500 Barcelona, Espana');
SELECT id, value, COUNT(*) OVER (PARTITION BY id) AS n, ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT NULL)) AS rn, adress
FROM test
CROSS APPLY STRING_SPLIT(adress, ',')
在我看来,你们把事情搞得太复杂了。只需要创建一个CLR UDF就可以了。
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
public partial class UserDefinedFunctions {
[SqlFunction]
public static SqlString SearchString(string Search) {
List<string> SearchWords = new List<string>();
foreach (string s in Search.Split(new char[] { ' ' })) {
if (!s.ToLower().Equals("or") && !s.ToLower().Equals("and")) {
SearchWords.Add(s);
}
}
return new SqlString(string.Join(" OR ", SearchWords.ToArray()));
}
};
你可以在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);