Function to Split a Delimited String into a Table

• Andy B.

  SSC Eights!

  Points: 890

  More actions

  September 24, 2007 at 5:10 am

  #181192

  Comments posted to this topic are about the item Function to Split a Delimited String into a Table

  
  Enjoy!
  
  A Brown
  
  Manage all of your backups in one simple job using this script.

• philcart

  SSC-Forever

  Points: 47795

  More actions

  September 24, 2007 at 6:10 am

  #735585

  SQL 2005 variant that doesn't use a loop.

  http://philcart.blogspot.com/2007/06/split-function.html

  Could be easily modified to work with nvarchar(4000)

  --------------------
  Colt 45 - the original point and click interface

• Stylez

  SSCrazy

  Points: 2195

  More actions

  February 13, 2008 at 11:59 pm

  #778214

  Hi good Sir

  thanks for doing the string split function. i would like to kindly ask if you have a sample code for my scenario below;

  Column 1 = ID

  Column 2 = Lastname

  Column 3 = Job Description

  and then i will declare a variable to be used in inserting the columns mentioned above

  declare @Value varchar(4000)

  set @value = 'IDValue,Lastnamevalue,JobDescriptionvalue'

  i noticed that you are inserting these values in a single column. is it possible to save these values in a seperate column named ID,Lastname and Job Description? your assistance is very much appreciated.

  Best Regards

  N.O.E.L.

• g.chakhidze

  Grasshopper

  Points: 17

  More actions

  March 9, 2011 at 2:04 am

  #1296363

  Hi,

  Thank you, I've found this function very useful.

  There is a little typo at the line

  SET @list = LTRIM(RTRIM(@list)) + ','

  Instead of concatenating with default value (,), you must concatenate with passed in @delimiter argument.

  SET @list = LTRIM(RTRIM(@list)) + @delimiter

  Without this, if you use delimiter different than comma, it will not add last row to the resulting table.

  For example:

  select * from DStringToTable(N'one|two', N'|')

  returns table

  value

  -----

  one

  which is missing value 'two'