How to use the SQL replace function effectively?

Question

I am trying to replace multiple rows in an Access database to follow a new set of data rules. For instance, the word Fort in Fort Myers is listed as Ft., Ft and Fort. I would like to make a global change to the group. I am familiar with the SQL replace command, but wondering if anyone has done something similar with a stored procedure or had experience with something like this.

Answer 1

Harder than it sounds to the lay person ...

There is no way around it but making a Replace for each thing you don't like, changing into what you do like. BUT BE VERY CAREFUL ... unintended consequences and all. I recommend doing a select before every update to see exactly what you will be updating.

So in your instance of Fort Myers you have to do 3 Replaces:

Replace("Ft. Myers", "Fort Myers")
Replace("Ft Myers", "Fort Myers")
Replace("Fort. Myers", "Fort Myers")

If you have much data and many things to change, this could be a HUGE task. But there is no "automated" way to do it - SQL does not use fuzzy logic, you have to specify exactly everything you want it to do.

Answer 2

Tidying addresses can be a nightmare. You may need to create a replace table:

ShouldBe    Current
Fort Myers  Ft Myers
Foot Hill   Ft Hills

For the most part, the ShouldBe column can be filled in with update queries, but you will also be able to run your eye over the results before updating the main table. This will also stand in good stead for future data entry.

Answer 3

You have to be really, really careful that you don't replace more than what you intend.

MAKE A BACKUP first in case things go horribly wrong.

Always start with a SELECT to filter the records first. Go over the results carefully.

SELECT * FROM Table WHERE City LIKE "%Ft. Myers%"

Then do the Replaces as Carlton said.

Answer 4

If your goal is to standardize the city names that are something like ~Fort Myers, you should be able to do something like this:

UPDATE Table SET City = 'Fort Myers' WHERE City LIKE 'F%Myers';

This should replace any City field in any row where the City begins with an F and ends in Myers. This may be what you want, but be very careful.

Answer 5

Thanks for the quick respones. I found a tool called sql find and replace for SQL 2005 and seems like an interesting tool if it is ever needed. anyone ever used this tool?