我有一个字符串,它看起来像这样
在SQL中该怎么做?
BAT | CAT | RAT | MAT
。我想将这个字符串分成四个部分,然后分别存储到四个不同的变量中,比如说@a,@b,@c和@d。在SQL中该怎么做?
BAT | CAT | RAT | MAT
。我想将这个字符串分成四个部分,然后分别存储到四个不同的变量中,比如说@a,@b,@c和@d。用于以字符分割:
DECLARE @A VARCHAR (100)= 'cat | bat | sat'
SELECT items
INTO #STRINGS
FROM dbo.split(@A,'|')
另请参阅此链接。
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 :如何将字符串拆分成数组并获取数组(x)?
字符串拆分的解决方法:
http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings
sysname
数据类型。只需进行普通的字符串分割即可。 - ErikEParseName
,因为这对我来说似乎是非常诱人的代码片段。 - SamuraiJack简单明了。(在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
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
|
分隔的字符串拆分并存储在表变量中。