SQL Server Forums
Profile | Register | 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
 New Topic  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

3741 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

3741 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
22769 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000