Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trying to create line items records from a string Expand / Collapse
Author
Message
Posted Monday, September 16, 2013 10:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 19, 2013 9:07 AM
Points: 47, Visits: 84
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




Post #1495173
Posted Monday, September 16, 2013 3:12 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:54 PM
Points: 952, Visits: 2,890
Hi Nick

You could try the following. It makes use of PARSENAME and Jeff Modens DelimitedSplit8K function that can be found here
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
Post #1495296
Posted Monday, September 16, 2013 7:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 19, 2013 9:07 AM
Points: 47, Visits: 84
Thanks a lot. Exactly what I'm looking for. Much obliged!
Best
Nick



Post #1495346
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse