SQLServerCentral / SQL Server 7,2000 / T-SQL / Need Split delimiter string into columns Solution / Latest Posts

RE: Need Split delimiter string into columns Solution

Jeff Moden — Fri, 26 Apr 2013 09:24:15 GMT

[quote][b]gauravupadhyay2009 (4/26/2013)[/b][hr]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 againGaurav UpadhyayIndia[/quote]Why would you need to use any of the code on this thread if you used BULK INSERT? And if the code is the WHILE loop code, you may have built in a bit of a performance problem into your code.

RE: Need Split delimiter string into columns Solution

gauravupadhyay — Fri, 26 Apr 2013 06:45:39 GMT

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 againGaurav UpadhyayIndia

RE: Need Split delimiter string into columns Solution

Andrius-794186 — Thu, 21 Mar 2013 07:10:17 GMT

Thanks for this function, Paul! :)

RE: Need Split delimiter string into columns Solution

Sean Lange — Tue, 04 Dec 2012 08:33:30 GMT

[quote][b]nick.wright 69367 (12/4/2012)[/b][hr]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 ;-). 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, AgainNick Wright[/quote]I hear ya there. Can't always change at the last minute. :-) 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.

RE: Need Split delimiter string into columns Solution

nick.wright 69367 — Tue, 04 Dec 2012 08:23:27 GMT

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 ;-). 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, AgainNick Wright

RE: Need Split delimiter string into columns Solution

Sean Lange — Tue, 04 Dec 2012 08:03:05 GMT

[quote][b]nick.wright 69367 (12/4/2012)[/b][hr]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.[/quote]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.

RE: Need Split delimiter string into columns Solution

nick.wright 69367 — Tue, 04 Dec 2012 02:11:02 GMT

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.

RE: Need Split delimiter string into columns Solution

Ed Phillips — Tue, 18 Oct 2005 05:34:00 GMT

Hi Paul / SM,

Thanks for this as well, just what I needed.

Regds,

RE: Need Split delimiter string into columns Solution

smunshi — Fri, 14 Oct 2005 09:22:00 GMT

Hi Paul,

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

Best Regards,

RE: Need Split delimiter string into columns Solution

Paul Cresham — Fri, 14 Oct 2005 08:50:00 GMT

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)ASBEGINIF @index = 0 RETURN( LEFT(@array, CHARINDEX(';', @array) - 1) )DECLARE @counter INTSELECT @counter = 0WHILE @counter < @indexBEGIN IF (CHARINDEX(';', @array) <> 0) SELECT @array = SUBSTRING(@array, CHARINDEX(';', @array) + 1, LEN(@array)) ELSE SELECT @array = '' SELECT @counter = @counter + 1ENDIF CHARINDEX(';', @array) != 0 SELECT @array = LEFT(@array, CHARINDEX(';', @array) - 1)RETURN( @array )ENDGOThen 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_tableHTH.

Need Split delimiter string into columns Solution

smunshi — Fri, 14 Oct 2005 08:34:00 GMT

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!!