Parsing a Text file that is Fixth field, and delimited

[JVZ]

A project that I working on requires me to delimit a text file that looks something like

Field1|Field2|Field3|Field4

Fields 1 - 3 are delimited by "|" however field 4 contains 220 characters and is fixed width delimited. I need to parse field 4 into 30 columns.

I was thinking about first loading this into a staging table, and then using subString method to parse that field and insert that into the table... However this method proves to be slow...

Can anyone suggest a better way?

 

[JayKusch]

String manipulations in SQL Server are always going to be slow. SS is not really designed to be a parser. You may want to take care of this busy work outside of SS and create a new file with all your cleansing done before hand.

Thanks

J. Kusch

 

[jer494]

look at using a regular expression parser to do the work of parsing the file. .NET has regex built in or you can download a java or perl string parser.