Solved

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

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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 …

776 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