I have a string which looks like BAT | CAT | RAT | MAT I want to split this string into 4 parts and then store them into 4 different variables say .. @a,@b,@c,@d respectively.
How can it be done in sql?
-
Use CHARINDEX, look here: stackoverflow.com/questions/10581772/…T McKeown– T McKeown2013-12-20 07:23:01 +00:00Commented Dec 20, 2013 at 7:23
-
is this helpful ,stackoverflow.com/questions/2647/split-string-in-sqlKumarHarsh– KumarHarsh2013-12-20 07:23:06 +00:00Commented Dec 20, 2013 at 7:23
-
As the problem you face this thread may help you stackoverflow.com/questions/6953115/…Fu March– Fu March2016-01-07 09:40:16 +00:00Commented Jan 7, 2016 at 9:40
Add a comment
|
3 Answers
for splitting around a 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)?
workarounds for splitting strings:
http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings
5 Comments
ErikE
ParseName is the standard clever answer, but it is actually not really a very good answer, because it is expecting the
sysname data type. Just do normal string shredding.
SamuraiJack
@ErikE can you please elaborate on why one should avoid
ParseName because this seems to be very alluring piece of code to me.SamuraiJack
Ok I just realized that one can split only 4 parameters, if there is a 5th one it would simply return null. Am I wrong?
gaurav5430
actually, you are right, please visit the links for more info.
ErikE
What if you want to use periods? What if you want strings longer than 255 characters? Have you looked at the code for ParseName--it's very ugly if I recall correctly. Abusing system functions gives me the willies. It just seems a bad idea. What if you only have 3 tokens instead of 4 and ask for #4?
Nice and simple. (Using PATINDEX in Microsoft SQL Server Management Studio.)
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
Comments
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.
2 Comments
SamuraiJack
Is there any simpler way? I have a function which takes care of splitting a long string of arguments but I was looking for some simple way to split a string when number of arguments are known. \
gotqn
This a well known technique for splitting strings and it is simple. If you have a function that is implementing this method you can use it anywhere you need.