使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
几乎所有其他答案都是替换正在分割的字符串,这浪费了CPU周期并执行不必要的内存分配。
我在这里介绍了一种更好的进行字符串拆分的方法:http://www.digitalruby.com/split-string-sql-server/
代码如下:
SET NOCOUNT ON
-- You will want to change nvarchar(MAX) to nvarchar(50), varchar(50) or whatever matches exactly with the string column you will be searching against
DECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL)
DECLARE @StringToSplit nvarchar(MAX) = 'your|string|to|split|here'
DECLARE @SplitEndPos int
DECLARE @SplitValue nvarchar(MAX)
DECLARE @SplitDelim nvarchar(1) = '|'
DECLARE @SplitStartPos int = 1
SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
WHILE @SplitEndPos > 0
BEGIN
SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos))
INSERT @SplitStringTable (Value) VALUES (@SplitValue)
SET @SplitStartPos = @SplitEndPos + 1
SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
END
SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647)
INSERT @SplitStringTable (Value) VALUES(@SplitValue)
SET NOCOUNT OFF
-- You can select or join with the values in @SplitStringTable at this point.
其他回答
这个问题不是关于字符串分割方法,而是关于如何获取第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
这个模式工作得很好,可以进行推广
Convert(xml,'<n>'+Replace(FIELD,'.','</n><n>')+'</n>').value('(/n[INDEX])','TYPE')
^^^^^ ^^^^^ ^^^^
注意字段,索引和类型。
让一些表具有类似的标识符
sys.message.1234.warning.A45
sys.message.1235.error.O98
....
然后,你就可以写作了
SELECT Source = q.value('(/n[1])', 'varchar(10)'),
RecordType = q.value('(/n[2])', 'varchar(20)'),
RecordNumber = q.value('(/n[3])', 'int'),
Status = q.value('(/n[4])', 'varchar(5)')
FROM (
SELECT q = Convert(xml,'<n>'+Replace(fieldName,'.','</n><n>')+'</n>')
FROM some_TABLE
) Q
拆铸所有零件。
下面是一个SQL UDF,它可以分割字符串并只抓取特定的部分。
create FUNCTION [dbo].[udf_SplitParseOut]
(
@List nvarchar(MAX),
@SplitOn nvarchar(5),
@GetIndex smallint
)
returns varchar(1000)
AS
BEGIN
DECLARE @RtnValue table
(
Id int identity(0,1),
Value nvarchar(MAX)
)
DECLARE @result varchar(1000)
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
select @result = value from @RtnValue where ID = @GetIndex
Return @result
END
解析姓和名的简单解决方案
DECLARE @Name varchar(10) = 'John Smith'
-- Get First Name
SELECT SUBSTRING(@Name, 0, (SELECT CHARINDEX(' ', @Name)))
-- Get Last Name
SELECT SUBSTRING(@Name, (SELECT CHARINDEX(' ', @Name)) + 1, LEN(@Name))
在我的例子中(在许多其他人中似乎也是如此……),我有一个由一个空格隔开的姓和名列表。可以直接在选择语句中使用它来解析姓和名。
-- i.e. Get First and Last Name from a table of Full Names
SELECT SUBSTRING(FullName, 0, (SELECT CHARINDEX(' ', FullName))) as FirstName,
SUBSTRING(FullName, (SELECT CHARINDEX(' ', FullName)) + 1, LEN(FullName)) as LastName,
From FullNameTable
我知道这是一个老问题,但我认为有人可以从我的解决方案中受益。
select
SUBSTRING(column_name,1,CHARINDEX(' ',column_name,1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
,1
,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)+1
,LEN(column_name))
from table_name
SQL小提琴
优点:
它用' '分隔所有3个子字符串。 不能使用while循环,因为它会降低性能。 不需要枢轴,因为所有的结果子字符串将显示在 一行
限制:
一个人必须知道绝对的不。Of Spaces(子字符串)。
注:解决方案可以给出最多N个子字符串。
为了克服这个限制,我们可以使用下面的参考。
但是上面的解决方案不能在表中使用(实际上我不能使用它)。
我希望这个解决方案能帮助到一些人。
更新:在记录> 50000的情况下,不建议使用LOOPS,因为它会降低性能