SQL 如何在sql中将字符串拆分为变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20698318/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to split a string into variables in sql?
提问by SamuraiHyman
I have a string which looks like BAT | CAT | RAT | MATI want to split this string into 4 parts and then store them into 4 different variables say .. @a,@b,@c,@d respectively.
我有一个字符串，看起来BAT | CAT | RAT | MAT我想将此字符串拆分为 4 部分，然后将它们存储到 4 个不同的变量中，例如 .. @a、@b、@c、@d。
How can it be done in sql?
如何在sql中完成？
回答by gaurav5430
for splitting around a char :
用于围绕 char 拆分：
DECLARE @A VARCHAR (100)= 'cat | bat | sat'
SELECT items
INTO #STRINGS
FROM dbo.split(@A,'|')
also see this link
另请参阅此链接
DECLARE @test varchar(max);
set @test = 'Peter/Parker/Spiderman/Marvel';
set @test = Replace(@test, '/', '.');
SELECT ParseName(@test, 4) --returns Peter
SELECT ParseName(@test, 3) --returns Parker
SELECT ParseName(@test, 2) --returns Spiderman
SELECT ParseName(@test, 1) --returns Marvel
SQL Server 2005 : split string into array and get array(x)?
SQL Server 2005：将字符串拆分为数组并获取数组（x）？
workarounds for splitting strings:
拆分字符串的解决方法：
http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings
http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings
回答by gotqn
You can split the values and insert them in a table variable, then assign them to your variables like this:
您可以拆分值并将它们插入表变量中，然后将它们分配给您的变量，如下所示：
DECLARE @DataSource TABLE
(
[ID] TINYINT IDENTITY(1,1)
,[Value] NVARCHAR(128)
)
DECLARE @Value NVARCHAR(MAX) = 'BAT | CAT | RAT | MAT'
DECLARE @XML xml = N'<r><![CDATA[' + REPLACE(@Value, '|', ']]></r><r><![CDATA[') + ']]></r>'
INSERT INTO @DataSource ([Value])
SELECT RTRIM(LTRIM(T.c.value('.', 'NVARCHAR(128)')))
FROM @xml.nodes('//r') T(c)
SELECT [ID]
,[Value]
FROM @DataSource
The result if this query is:
如果此查询的结果是：
Note, this technique is dynamic - it will split any count of strings split with |and store them in table variable table.
请注意，此技术是动态的 - 它将拆分与拆分的任何字符串计数|并将它们存储在表变量表中。
回答by Mat
Nice and simple. (Using PATINDEX in Microsoft SQL Server Management Studio.)
好看又简单。（在 Microsoft SQL Server Management Studio 中使用 PATINDEX。）
DECLARE @string varchar(25) = 'BAT | CAT | RAT | MAT'
DECLARE @one varchar(5) = null
DECLARE @two varchar(5) = null
DECLARE @three varchar(5) = null
DECLARE @four varchar(5) = null
BEGIN
SET @one = SUBSTRING(@string, 0, PATINDEX('%|%', @string))
SET @string = SUBSTRING(@string, LEN(@one + '|') + 1, LEN(@string))
SET @two = SUBSTRING(@string, 0, PATINDEX('%|%', @string))
SET @string = SUBSTRING(@string, LEN(@two + '|') + 1, LEN(@string))
SET @three = SUBSTRING(@string, 0, PATINDEX('%|%', @string))
SET @string = SUBSTRING(@string, LEN(@three + '|') + 1, LEN(@string))
SET @four = @string
SELECT @one AS Part_One, @two AS Part_Two, @three AS Part_Three, @four AS Part_Four
END
