Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query

Posted on 2011-09-23
7
Medium Priority
?
330 Views
Last Modified: 2012-05-12
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

John
0
Comment
Question by:pepps11976
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 6

Expert Comment

by:MuffyBunny
ID: 36587495
What types? Can you tell us an example of a specific piece of data you would want to extract from all of that?

Just out of curiosity, why the huge memo instead of individual columns per piece of info?
0
 

Author Comment

by:pepps11976
ID: 36587535
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
0
 
LVL 6

Expert Comment

by:MuffyBunny
ID: 36587632
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'?
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

Author Comment

by:pepps11976
ID: 36587635
Yes it can be
0
 
LVL 6

Accepted Solution

by:
MuffyBunny earned 2000 total points
ID: 36587726
I created a table named memotest with 1 column named bigmemo. I populated it with the data sample you provided. Assuming the data always follows at least a semi-consistent format, something like this will do it:

SELECT SUBSTRING(bigmemo, CHARINDEX('Foreign End User: ', bigmemo) + 19, (CHARINDEX(',', bigmemo) - CHARINDEX('Foreign End User: ', bigmemo) - 19)) as col1, 
	SUBSTRING(bigmemo, CHARINDEX(',', bigmemo) + 2, (CHARINDEX('Manufacturer of', bigmemo) - CHARINDEX(',', bigmemo) - 3)) as col2
from memotest

Open in new window

0
 

Author Comment

by:pepps11976
ID: 36587848
Brilliant this seems perfect, One question will this allow for longer addresses and names etc
0
 
LVL 6

Expert Comment

by:MuffyBunny
ID: 36587977
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' . If that happens, 'someplacebigger' will become part of colunn2 data. If you're confident that wont happen, it's all good.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question