Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trying to create line items records from a string


Trying to create line items records from a string

Author
Message
al_nick
al_nick
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 85
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



mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
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
al_nick
al_nick
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 85
Thanks a lot. Exactly what I'm looking for. Much obliged!
Best
Nick



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search