How to use LIKE query and UPDATE statement? * Example provided *

Question

I was just starting to learn SQL, so I'm not so good at it yet, and I was not sure how to do it, because I could not correctly formulate the question, so I decided to ask the question myself.

Okay, so here is what I'm trying to do in my particular case: I am using Microsoft Access 2010. In my table called "MEMBERS" I have the field "Address". The entries in this column are very dirty, so I'm trying to remove it. Some of the entries contain "Road", while others contain "Rd". I want to update the columns that have “Road” and change this word only to “Rd” without affecting the rest of the address.

So, if one of the entries was “7 Example Road”, I would like to create a query to change it to “7 Example Rd”.

This is the query I was trying to use:

UPDATE MEMBERS SET 'Rd' WHERE Address LIKE '*Road' 

Any help would be greatly appreciated!

Answer 1

Try using the REPLACE() function.

 UPDATE MEMBERS SET Address = REPLACE(Address, 'Road', 'Rd') WHERE Address LIKE '*Road'