Need Split delimiter string into columns Solution

Question

Need Split delimiter string into columns Solution

smunshi

Old Hand

Points: 349
More actions
October 14, 2005 at 8:34 am

#363927

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

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 1

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.

smunshi Old Hand Points: 349 More actions · Answer 2

Hi Paul,

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

Best Regards,

SM

Ed Phillips SSChasing Mays Points: 634 More actions · Answer 3

Hi Paul / SM,

Thanks for this as well, just what I needed.

Regds,

EP

Ed Phillips

nick.wright 69367 SSC Enthusiast Points: 136 More actions · Answer 4

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.

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

nick.wright 69367 (12/4/2012)
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.

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

nick.wright 69367 SSC Enthusiast Points: 136 More actions · Answer 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 ;-). 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

Sean Lange SSC Guru Points: 286573 More actions · Answer 7

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 ;-). 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

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.

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Andrius-794186 SSC Veteran Points: 225 More actions · Answer 8

Andrius-794186

SSC Veteran

Points: 225

March 21, 2013 at 7:10 am

#1599361

Thanks for this function, Paul! 🙂

gauravupadhyay SSC Enthusiast Points: 120 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 10

gauravupadhyay2009 (4/26/2013)
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

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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

syamkakarla Valued Member Points: 72 More actions · Answer 11

syamkakarla

Valued Member

Points: 72

July 20, 2013 at 9:19 pm

#1634287

Thanks for the code paul. It helped me a lot.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 12

syamkakarla (7/20/2013)
Thanks for the code paul. It helped me a lot.

Take a look at the Green and Blue lines in the following chart and compare them to the skinny black line. The Green and Blue lines are two different methods using WHILE loops. Don't use WHILE loops to do splits. Please see the following article for a much better method of splitting VARCHAR(8000) or less.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

satishrao Newbie Points: 3 More actions · Answer 13

Hi Guys ,

I have column with Values like 1111\123132\34342323\1212. I used the above code to split it . But this doesn't work. The code returns blank. Can you please look into this and provide some help if possible ?

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 14

satishrao wrote:

Hi Guys ,
I have column with Values like 1111\123132\34342323\1212. I used the above code to split it . But this doesn't work. The code returns blank. Can you please look into this and provide some help if possible ?

Do you have SQL Server 2012 or above or do you have something less than SQL Server 2012?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)