September 16, 2013 at 10:10 am
Hi all-
I've got an invoice coming in as a single sting and am trying to figure out how to best break the string up into individual line item records:
The string looks like this (disregard carriage returns):
HEADER*CompanyA*CompanyB*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^
And I'm trying to get the resultant table to look like this:
DATE | ToCompany | FromCompany |InvoiceNum | ItemCategory|ItemCategoryCode| ItemDescr|ItemCode
8/22/2009 CompanyA CompanyB 5001 Parts PRT PartA 11
8/22/2009 CompanyA CompanyB 5001 Parts PRT PartF 5
8/22/2009 CompanyA CompanyB 5001 Parts PRT PartF 37
8/22/2009 CompanyA CompanyB 5001 Supplies SUP SupplyK 1
8/22/2009 CompanyA CompanyB 5001 Supplies SUP SupplyY 88
8/22/2009 CompanyA CompanyB 5001 Supplies SUP SupplyG 72
8/22/2009 CompanyA CompanyB 5001 Materials MTR MaterialQ 202
Any suggestions would be GREATLY appreciated.
Thanks
Nick
September 16, 2013 at 3:12 pm
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
September 16, 2013 at 7:15 pm
Thanks a lot. Exactly what I'm looking for. Much obliged!
Best
Nick
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply