Split String

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Split String

Split String

Rate Topic

Display Mode

Topic Options

Author

Message

raym

Posted Tuesday, July 01, 2008 11:35 AM

SSC 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

Vijaya Kadiyala

Posted Tuesday, July 01, 2008 12:35 PM

SSCommitted

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

GSquared

Posted Tuesday, July 01, 2008 3:27 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

raym

Posted Tuesday, July 01, 2008 5:25 PM

SSC 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

raym

Posted Tuesday, July 01, 2008 5:27 PM

SSC 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

GSquared

Posted Wednesday, July 02, 2008 8:25 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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 »

Permissions