Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Split String Expand / Collapse
Author
Message
Posted Tuesday, July 1, 2008 11:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 26, 2009 7:37 AM
Points: 87, Visits: 307
How can I split this string into the columns in the first line so I can insert it into a table
with the 14 Columns

if object_id('tempdb..#test') is not null
drop table #test
go
Create Table #test (String varchar(8000))
Insert #test Values ('''"Col1","Col2","Col3","Col4","Col5","Col6","Col7","Col8","Col9","Col10","Col11","Col12","Col13","Col14"')
Insert #test Values ('''125020000065417,0220,01,00, ,2008/06/30 08:14:28,710,500.000000,710,500.000000,424477,00000000,113927,Dont worry about it')
Insert #test Values ('''214570001560883,0220,00,00, ,2008/06/30 08:14:46,710,30.000000,710,30.000000,424477,00000000,114001,Dont worry about it')
Insert #test Values ('''125020000395491,0100,01,00, ,2008/06/30 08:15:53,710,420.000000,710,420.000000,424477,82260001,114063,Dont worry about it')
Insert #test Values ('''125020000067371,0100,30,00, ,2008/06/30 08:33:24,710,0.000000,710,0.000000,424477,80900001,114069,Dont worry about it')
Insert #test Values ('''125020000367698,0100,01,00, ,2008/06/30 08:39:16,710,500.000000,710,500.000000,424477,TL335040,114074,Dont worry about it')
Insert #test Values ('''125020000367698,0100,01,00, ,2008/06/30 08:41:14,710,500.000000,710,500.000000,424477,TL335040,114075,Dont worry about it')
Insert #test Values ('''125020000067371,0100,01,00, ,2008/06/30 08:43:14,710,380.000000,710,380.000000,424477,80900001,114076,Dont worry about it')
Insert #test Values ('''125020000345629,0100,01,00, ,2008/06/30 09:17:14,710,1000.000000,710,1000.000000,424477,SASC3021,114094,Dont worry about it')
Insert #test Values ('''125020000462424,0100,30,00, ,2008/06/30 09:21:22,710,0.000000,710,0.000000,424477,80450001,114098,Dont worry about it')
Insert #test Values ('''125020000336693,0100,30,00, ,2008/06/30 09:21:42,710,0.000000,710,0.000000,424477,80970001,114099,Dont worry about it')
Insert #test Values ('''125020000420281,0100,01,00, ,2008/06/30 09:33:04,710,1200.000000,710,1200.000000,424477,87670001,114104,Dont worry about it')


select * from #test



Post #526829
Posted Tuesday, July 1, 2008 12:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 25, 2010 6:09 AM
Points: 1,621, Visits: 409
Hi,
Check out the below link
http://www.dotnetspider.com/resources/1266-Split-e-String-Using-char-separator.aspx

Thanks -- Vj
http://dotnetvj.blogspot.com


Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers



Post #526862
Posted Tuesday, July 1, 2008 3:27 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
http://www.sqlservercentral.com/articles/TSQL/62867/

That article has data on how to split a delimited string efficiently, and a bunch of other good stuff that's related to that.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #526930
Posted Tuesday, July 1, 2008 5:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 26, 2009 7:37 AM
Points: 87, Visits: 307
BUt this splits the string into one column. I want to split it into 14 columns so the result set is like
col1 col2 col3 ..........

how do I achieve that ?



Post #526960
Posted Tuesday, July 1, 2008 5:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 26, 2009 7:37 AM
Points: 87, Visits: 307
How is this procedure invoked if the input is in a table #test


Post #526962
Posted Wednesday, July 2, 2008 8:25 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
This article has data on splitting into multiple columns:

http://www.sqlservercentral.com/articles/T-SQL/63003/


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #527369
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse