Solved

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

Posted on 2011-10-01
3
246 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

16 Experts available now in Live!

Get 1:1 Help Now