10

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?

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
SamuraiJack
  • 5,131
  • 15
  • 89
  • 195
  • Use CHARINDEX, look here: http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns – T McKeown Dec 20 '13 at 07:23
  • is this helpful ,http://stackoverflow.com/questions/2647/split-string-in-sql – KumarHarsh Dec 20 '13 at 07:23
  • As the problem you face this thread may help you http://stackoverflow.com/questions/6953115/dividing-a-string-into-two-parts-and-selecting-into-two-variables – Fu March Jan 07 '16 at 09:40

3 Answers3

14

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

Community
  • 1
  • 1
gaurav5430
  • 12,934
  • 6
  • 54
  • 111
  • 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. – ErikE Dec 20 '13 at 07:42
  • @ErikE can you please elaborate on why one should avoid `ParseName` because this seems to be very alluring piece of code to me. – SamuraiJack Dec 20 '13 at 09:25
  • 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? – SamuraiJack Dec 20 '13 at 10:34
  • actually, you are right, please visit the links for more info. – gaurav5430 Dec 20 '13 at 10:47
  • 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? – ErikE Dec 21 '13 at 06:22
9

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
Mat
  • 91
  • 1
  • 4
7

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:

enter image description here

Note, this technique is dynamic - it will split any count of strings split with | and store them in table variable table.

gotqn
  • 42,737
  • 46
  • 157
  • 243
  • 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. \ – SamuraiJack Dec 20 '13 at 10:36
  • 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. – gotqn Dec 20 '13 at 11:01