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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 splitting string into multiple columns
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

joe8079
Posting Yak Master

USA
127 Posts

Posted - 02/06/2013 :  08:57:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 02/06/2013 :  09:34:16  Show Profile  Reply with Quote
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.
Go to Top of Page

joe8079
Posting Yak Master

USA
127 Posts

Posted - 02/06/2013 :  10:17:04  Show Profile  Reply with Quote
thank you, I will give that a shot
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 02/06/2013 :  10:27:53  Show Profile  Reply with Quote
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;

Edited by - James K on 02/06/2013 10:29:00
Go to Top of Page

joe8079
Posting Yak Master

USA
127 Posts

Posted - 02/06/2013 :  11:49:57  Show Profile  Reply with Quote
thanks James, i'm going to test it out
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 02/08/2013 :  07:54:34  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000