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

x
?
Solved

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

Posted on 2011-10-01
3
Medium Priority
?
261 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
[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
3 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

721 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