Need Split delimiter string into columns Solution

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Need Split delimiter string into columns...

11 posts, Page 1 of 2

1 2 »»

Need Split delimiter string into columns Solution

Rate Topic

Display Mode

Topic Options

Author

Message

smunshi

Posted Friday, October 14, 2005 8:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 09, 2010 7:04 PM
Points: 3, Visits: 13

Hi All,

I need help with a string with delimiter that needs to be split into column names. I have been looking for this solution for a while. Specifically what I want to do is noted below...

Currently:

I have a table with a string with delimiter column, and I need to split this string into columns as shown below: Note that the cat and subcat can vary in length...

'/cat/subcat1/subcat2/subcat3/subcat4/subcat5'

What I need is a t-sql that pivots this list into new columns as shown below:

cat subcat1 subcat2 subcat3 subcat4 subcat5
------ -------- -------- -------- -------- --------
School Math Science History Arts PE

Also I need a way to insert these new columns into the existing table that has the original string with delimiter...

Thanks!!

Post #229031

Paul Cresham

Posted Friday, October 14, 2005 8:50 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 04, 2009 7:44 AM
Points: 263, Visits: 44

I just did something similar the other day with Progress arrays, which are returned to SQL Server as a single semicolon-delimited column, and I needed to split out one such array into several columns.

This is the function I wrote... you would need to replace the semicolon (

with your own delimiter (a slash?).

CREATE FUNCTION dbo.FromProgressArray (@array VARCHAR(4000), @index INT)
RETURNS VARCHAR(4000)
AS
BEGIN

IF @index = 0
RETURN( LEFT(@array, CHARINDEX(';', @array) - 1) )

DECLARE @counter INT
SELECT @counter = 0

WHILE @counter < @index
BEGIN
IF (CHARINDEX(';', @array) <> 0)
SELECT @array = SUBSTRING(@array, CHARINDEX(';', @array) + 1, LEN(@array))
ELSE
SELECT @array = ''
SELECT @counter = @counter + 1
END

IF CHARINDEX(';', @array) != 0
SELECT @array = LEFT(@array, CHARINDEX(';', @array) - 1)

RETURN( @array )

END
GO

Then you can do this:

INSERT INTO table (cat, subcat1, subcat2, subcat3, subcat4, subcat5)
SELECT dbo.FromProgressArray(sourcecol, 0), dbo.FromProgressArray(sourcecol, 1), dbo.FromProgressArray(sourcecol, 2), dbo.FromProgressArray(sourcecol, 3), dbo.FromProgressArray(sourcecol, 4), dbo.FromProgressArray(sourcecol, 5)
FROM source_table

HTH.

Post #229038

smunshi

Posted Friday, October 14, 2005 9:22 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 09, 2010 7:04 PM
Points: 3, Visits: 13

Hi Paul,

This works Great!!!! Thanks so much, I have been splitting my hair for this solution...again Thank you!!!

Best Regards,

Post #229047

Ed Phillips

Posted Tuesday, October 18, 2005 5:34 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 10, 2009 8:01 AM
Points: 190, Visits: 19

Hi Paul / SM,

Thanks for this as well, just what I needed.

Regds,

Ed Phillips

Post #229670

nick.wright 69367

Posted Tuesday, December 04, 2012 2:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:07 AM
Points: 2, Visits: 6

Thank you very much for this. I wouldn't normally use a while loop in a function, but looking at the replace/substring code I've written in the past to get, say, the 5th element in a delimited string I'm more than happy to use it here!

My only amendment was to add the delimiter as a parameter, to make it more extensible.

Post #1392292

Sean Lange

Posted Tuesday, December 04, 2012 8:03 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:12 PM
Points: 8,619, Visits: 8,260

nick.wright 69367 (12/4/2012)

Nick,

Unfortunately you found a very old thread with a VERY out of date methodology for splitting delimited strings. Please take a look at the link in my signature for MUCH improved way to split strings in sql.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1392515

nick.wright 69367

Posted Tuesday, December 04, 2012 8:23 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 8:07 AM
Points: 2, Visits: 6

That's great, Sean. Unfortunately, being an "agile" kind of developer, the ink on my code is still wet as it goes into a live environment Wink

. The new improved code will have to wait until the next iteration. Fortunately, its in a batch job that runs overnight and performance isn't (yet) an issue. However, I'll read your article and try to learn from it!

Thanks, Again

Nick Wright

Post #1392532

Sean Lange

Posted Tuesday, December 04, 2012 8:33 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:12 PM
Points: 8,619, Visits: 8,260

nick.wright 69367 (12/4/2012)

That's great, Sean. Unfortunately, being an "agile" kind of developer, the ink on my code is still wet as it goes into a live environment Wink

I hear ya there. Can't always change at the last minute. Smile

It isn't my article but one written by Jeff Moden. That article has the ability to completely change the way you think about data. It will present you initially with the concept of a tally table which is probably the most useful technique in a sql developers tool box. Once you fully understand the tally table, the string splitter portion makes total sense.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1392545

Andrius-794186

Posted Thursday, March 21, 2013 7:10 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 4:45 AM
Points: 84, Visits: 76

Thanks for this function, Paul! :)

Post #1433768

gauravupadhyay

Posted Friday, April 26, 2013 6:45 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 20, 2013 3:43 AM
Points: 13, Visits: 8

Thanks a lot for writing this wonderful SQL Code. it gave me relax that i could not get 2 days.
i used this code in BULK Insert from .txt file data.with delim (|) .
thanks again

Gaurav Upadhyay
India

Post #1446934

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions