How to split a string into variables in sql?

for splitting around a char :

DECLARE @A VARCHAR (100)= 'cat | bat | sat'SELECT itemsINTO #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 PeterSELECT ParseName(@test, 3) --returns ParkerSELECT ParseName(@test, 2) --returns SpidermanSELECT ParseName(@test, 1) --returns Marvel

SQL Server 2005 : split string into array and get array(x)?

workarounds for splitting strings:

http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

Nice and simple.(Using PATINDEX in Microsoft SQL Server Management Studio.)

DECLARE @string varchar(25) = 'BAT | CAT | RAT | MAT'DECLARE @one varchar(5) = nullDECLARE @two varchar(5) = nullDECLARE @three varchar(5) = nullDECLARE @four varchar(5) = nullBEGIN      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_FourEND 

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.