July 1, 2008 at 11:35 am
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
July 1, 2008 at 12:35 pm
Hi,
Check out the below link
http://www.dotnetspider.com/resources/1266-Split-e-String-Using-char-separator.aspx
Thanks -- Vj
July 1, 2008 at 3:27 pm
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
July 1, 2008 at 5:25 pm
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 ?
July 1, 2008 at 5:27 pm
How is this procedure invoked if the input is in a table #test
July 2, 2008 at 8:25 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply