splitting string into multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

splitting string into multiple columns

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2013-02-06 : 08:57:05

I have a column below and I want to split that column into multiple based on the "/" character. for example, on the second row, 89 and 79 should be split into two rows as below:

ID
89
89/70
89/70/71
89/70/72
89/70/73
89/85
89/88
89/88/80
89/88/81
89/88/82

Desired output:
89
89 70
89 70 71
89 70 72

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-06 : 09:34:16

Use a string splitter function such as the one described in this article: http://www.sqlservercentral.com/articles/Tally+Table/72993/

If you have only 3 tokens, you could do this using T-SQL functions such as REPLACE, STUFF etc., but it is simpler to use a string splitter function.

joe8079
Posting Yak Master

127 Posts

Posted - 2013-02-06 : 10:17:04

thank you, I will give that a shot

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-06 : 10:27:53

Sure. If you run into problems, reply back. It is really simple to use - copy the function from the article - it is under the heading "The Final "New" Splitter Code, Ready for Testing". Run it in a query window to install the function. Then use it like in the example below

CREATE TABLE #tmp(Id VARCHAR(32));
INSERT INTO #tmp VALUES ('89'),('89/70'),('89/71');

SELECT
	Id,
	[1] AS Col1,
	[2] AS Col2,
	[3] AS Col3
FROM
	#tmp t 
	CROSS APPLY (SELECT * FROM 
	dbo.DelimitedSplit8K(Id,'/')) d
PIVOT (MAX(Item) FOR ItemNumber IN ([1],[2],[3]))P

DROP TABLE #tmp;

joe8079
Posting Yak Master

127 Posts

Posted - 2013-02-06 : 11:49:57

thanks James, i'm going to test it out

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-02-08 : 07:54:34

Also try this http://beyondrelational.com/modules/2/blogs/70/posts/10844/splitting-delimited-data-to-columns-set-based-approach.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources