pepps11976
asked on
SQL Query
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_mem
dbo.itran.it_stock, dbo.itran.it_quan, dbo.itran.it_dtedelv,dbo.i
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
John
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_mem
dbo.itran.it_stock, dbo.itran.it_quan, dbo.itran.it_dtedelv,dbo.i
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
John
ASKER
The huge memo its just the way our system works we do not have the ability to add columns
i did state in the above post what i need to extract sorry if it was unclear, from this peice
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
i need everything after Foreign End User but no furthrt than the comm ie "The State of the Netherlands"
that needs to be in one column and lastly i need everything after the comma but to stop where it starts Manufacturer ie "ODC "Het Arsenaal", Rijkszee-en Marinehaven, 1781 ZZ Den Helder, The Netherlands"
hope this helps
john
i did state in the above post what i need to extract sorry if it was unclear, from this peice
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
i need everything after Foreign End User but no furthrt than the comm ie "The State of the Netherlands"
that needs to be in one column and lastly i need everything after the comma but to stop where it starts Manufacturer ie "ODC "Het Arsenaal", Rijkszee-en Marinehaven, 1781 ZZ Den Helder, The Netherlands"
hope this helps
john
My bad. I missed the section where you said what you need.
Is that always the format? 'Foreign End User:' with 2 spaces?
Then the actual address always followed by 'Manufacturer of Commodity'?
Is that always the format? 'Foreign End User:' with 2 spaces?
Then the actual address always followed by 'Manufacturer of Commodity'?
ASKER
Yes it can be
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brilliant this seems perfect, One question will this allow for longer addresses and names etc
as long as it follows the format of 'Foreign End User: ' column1 data ',' column2 data 'Manufacturer of' the number of characters in the data is irrelevant.
Where you could run into problems would be if the column1 data actually has a comma in it. For example 'someplace,someplacebigger
Where you could run into problems would be if the column1 data actually has a comma in it. For example 'someplace,someplacebigger
Just out of curiosity, why the huge memo instead of individual columns per piece of info?