?
Solved

Text between 2 constants

Posted on 2011-05-03
7
Medium Priority
?
211 Views
Last Modified: 2012-05-11
I have a field that ALWAYS has this format:  

"Dough, John P  VicePresident  Acon  Development 148 DEV/Tickles"
"Smith, Mike A  President  Dcon  Development 148 DCO/Throwback"
"Franks, Blaine J  Worker Bee  Dcon  Development 148 DCO/Tossables"
"King, William B  VicePresident  Bcon  Development 148 BCO/Janitor"

results:
148 DEV
148 DCO
148 DCO
148 BCO

In a QUERY, I want to extract everything past the word DEVELOPMENT, up to and NOT including the /.

"Development" is a Constant, and always has a single space after.  the "/" is constant, always there.

Thanks!
0
Comment
Question by:TIgerV
  • 4
  • 2
7 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 35517569
Have a look at query1 in the sample
strings.mdb
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35517624
check this.
SELECT String, trim(mid(String,
           instr(1,String,"Development")+len("Development"),
           instr(1,String,"/")-instr(1,String,"Development")-len("Development"))) AS Sub_String
FROM your_table;

Open in new window

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35517655
Not having seen what Nick67's actual query is...


SELECT TheText, Mid(TheText, InStr(1, TheText, "Development") + 12, InStr(1, TheText, "/") - InStr(1, TheText, "Development") - 12) AS PortionOfText
FROM SomeTable

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:Nick67
ID: 35517696
LOL
SELECT Table1.Field1, InStr([field1],"development") AS Dev, Right([field1],Len([field1])-[dev]-11) AS trimmed, Replace([trimmed],"/"," ") AS Done
FROM Table1;

12 actually trims out one too many characters with Instr()
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35517717
>>12 actually trims out one too many characters with Instr()

In my testing, 12 is working just fine, actually.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35517727
And if we were being extra fancy for fun, and ONLY wanted the trimmed string:

SELECT trimmed
FROM [SELECT Table1.Field1, InStr([field1],"development") AS Dev, Right([field1],Len([field1])-[dev]-11) AS trimmed, Replace([trimmed],"/"," ") AS Done
FROM Table1]. AS temptable;
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35517744
I didn't use Mid.
And 12 did whack one character too many, because that was my first guess

Why?
Dunno
Maybe Instr() and mid() don't return exactly the same thing

Nick67 :)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

839 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