Posted on 2011-09-23
Ok i posted earlier a similar question but soon realised i need more help so here goes.
Trying to get certain types of text out of a memo column in my table, each memo feild will have similar data to below the only difference would be character lengths.
With reference to maales Spec: 102-004440 - issue 4
Notes of Spec
Export Licence No. 0123456789
Line Item 4
maales Part no. 206-058943-t4338-0026 002
Part Revision No: 002
Foreign End User: The State of the Netherlands, ODC "Het Arsenaal", Rijkszee-en Marinehaven, 1781 ZZ Den Helder, The Netherlands
Manufacturer of Commodity: hicrosemin Lovell
Ok what i need is any information after where it says Foreign end User: but only up to the comma, so in this case "The State of the Netherlands" in a seperate column.
and also in another column any thing after the comma so in this case "ODC "Het Arsenaal", Rijkszee-en Marinehaven, 1781 ZZ Den Helder, The Netherlands" but not including where it starts to say "Manufacturer of Commodity" the characters will be different in all cases.
my current view looks like this
SELECT TOP (100) PERCENT dbo.ihead.ih_sorder, SUBSTRING(dbo.itran.it_memo, CHARINDEX('No.', dbo.itran.it_memo)+4,9)As ExportNo, dbo.itran.it_anal,
dbo.itran.it_stock, dbo.itran.it_quan, dbo.itran.it_dtedelv,dbo.itran.it_memo
FROM dbo.ihead LEFT OUTER JOIN
dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE (dbo.itran.it_memo LIKE '%Foreign End User%') AND (dbo.ihead.ih_sorder = 'ORD15686') AND (dbo.itran.it_status = 'a') AND (dbo.itran.it_recno = '3')
ORDER BY dbo.itran.it_recno