Split String

  • 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

  • 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

  • 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 ?

  • How is this procedure invoked if the input is in a table #test

  • 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