Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Function to Split a Delimited String into a Table Expand / Collapse
Author
Message
Posted Monday, September 24, 2007 5:10 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 1:33 PM
Points: 162, Visits: 66
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
Posted Monday, September 24, 2007 6:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 15, 2014 7:14 PM
Points: 2,693, Visits: 1,223
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
Posted Wednesday, February 13, 2008 11:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:42 PM
Points: 62, Visits: 410
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
Posted Wednesday, March 9, 2011 2:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 6, 2014 8:22 AM
Points: 1, Visits: 14
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 »

Add to briefcase

Permissions Expand / Collapse