SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Split delimiter string into columns Solution


Need Split delimiter string into columns Solution

Author
Message
smunshi
smunshi
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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!!





Paul Cresham
Paul Cresham
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 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.
smunshi
smunshi
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 13

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
Ed Phillips
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 19

Hi Paul / SM,

Thanks for this as well, just what I needed.

Regds,

EP





Ed Phillips
nick.wright 69367
nick.wright 69367
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26119 Visits: 17538
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
nick.wright 69367
nick.wright 69367
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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 ;-). 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
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26119 Visits: 17538
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Andrius-794186
Andrius-794186
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 93
Thanks for this function, Paul! Smile
gauravupadhyay
gauravupadhyay
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 25
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search