Hi Nick

You could try the following. It makes use of PARSENAME and Jeff Modens DelimitedSplit8K function that can be found here[/url]

declare @line varchar(8000) = 'HEADER*Company.A*Company.B*5001*8/22/2009^CATEGORY*Parts*PRT^LINE*PartA*11^LINE*PartF*5^LINE*PartG*37^CATEGORY*Supplies*SUP^LINE*SupplyK*1^LINE*SupplyY*88^LINE*SupplyG*72^CATEGORY*Materials*MTR^LINE*MaterialQ*202^';

select

-- Header components

replace(

parsename(

replace(replace(replace(

substring(@line,1,CHARINDEX('^',@line)-1) -- HEADER LINE

,'HEADER*',''),'.','^'),'*','.')

,1)

,'^','.') InvoiceDate

,replace(

parsename(

replace(replace(replace(

substring(@line,1,CHARINDEX('^',@line)-1)

,'HEADER*',''),'.','^'),'*','.')

,4)

,'^','.') ToCompany

,replace(

parsename(

replace(replace(replace(

substring(@line,1,CHARINDEX('^',@line)-1)

,'HEADER*',''),'.','^'),'*','.')

,3)

,'^','.') FromCompany

,replace(

parsename(

replace(replace(replace(

substring(@line,1,CHARINDEX('^',@line)-1)

,'HEADER*',''),'.','^'),'*','.')

,2)

,'^','.') InvoiceNum

-- Category Components

,substring(categoryLine,1,CHARINDEX('*',categoryLine)-1) category

,substring(categoryLine,CHARINDEX('*',categoryLine)+1,CHARINDEX('^',categoryLine)-CHARINDEX('*',categoryLine) - 1) categoryCode

-- Item Parts

,Item

,ItemCode

from

-- FETCH EACH OF THE CATEGORY LINES OUT

(

select ItemNumber categoryNum

,Item categoryLine

from dbo.DelimitedSplit8K(REPLACE(@line,'^CATEGORY*','~'),'~')

where itemnumber > 1

) c

-- AND CROSS APPLY EACH ITEM LINE TO THEM

CROSS APPLY (

select

ItemNumber ItemLineNumber

,REPLACE(

PARSENAME(

REPLACE(REPLACE(REPLACE(Item, 'LINE*',''),'.','^'),'*','.')

,2)

,'^','.') Item

,REPLACE(

PARSENAME(

REPLACE(REPLACE(REPLACE(Item, 'LINE*',''),'.','^'),'*','.')

,1)

,'^','.') ItemCode

from dbo.DelimitedSplit8K(categoryLine,'^')

where itemNumber > 1

and item <> '' --remove empty line at the end

) ip

Hope this helps

Edit: messed up link