Solved

Query to remove text from a field based on text in another field

Posted on 2011-10-01
3
254 Views
Last Modified: 2012-05-12
My data looks bit  like this

[Field1]                                    [Field2]
Joe Bloggs                               Joe Bloggs Family Business
Joe Bloggs                               Joe Bloggs Farm
John Smith                               John Smith Removals
Smith & Son                             Smith & Son Grocers
Jones                                       Jones The Butchers
Jones, Smith & Bloggs             Jones, Smith & Bloggs Solicitors

I would like a query (preferably) or a function to remove the contents of Field1 from Field2 and Trim the results i.e.

[Field2]
Family Business
Farm
Grocers
The Butchers
Solicitors
0
Comment
Question by:Ludique
3 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36896372
Try this:

UPDATE SomeTable
SET Field2 = Trim(Replace(Field2, Field1, ""))
WHERE Field2 Like "*" & Field1 & "*"

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36896376
0
 

Author Closing Comment

by:Ludique
ID: 36896442
Lovely.  Quick and Simple.  Thank you.

Sorry, I didn't check out the link as well as Matthewspatrick's solution worked instantly.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

914 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now