Function to Split a Delimited String into a Table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Andy Brown » Function to Split a Delimited String into a...

Function to Split a Delimited String into a Table

Rate Topic

Display Mode

Topic Options

Author

Message

Andy B.

Posted Monday, September 24, 2007 5:10 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 4:00 PM
Points: 162, Visits: 63

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.

Post #401951

philcart

Posted Monday, September 24, 2007 6:10 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:27 PM
Points: 2,692, Visits: 1,075

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)

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club

Post #401982

Stylez

Posted Wednesday, February 13, 2008 11:59 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:09 PM
Points: 47, Visits: 340

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.

Post #455533

g.chakhidze

Posted Wednesday, March 09, 2011 2:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 23, 2011 4:27 AM
Points: 1, Visits: 12

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'

Post #1075383

« Prev Topic | Next Topic »

Permissions