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.

Solution 1

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.

Solution 2

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.

Solution 3

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.

Solution 4

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.

View more solutions

Comments

[BIBD]

Yes... you don't want to make clbuttic mistake.

[onedaywhen]

Not sure if the 't-sql' tag is relevant but if they are using Jet/ACE (colloquially 'MS Access'), it would be best IMO to use the ALIKE keyword in place of LIKE, otherwise the engine would need to be in ANSI-92 Query Mode or the '%' wildcard characters to function correctly.

[onedaywhen]

Not sure if the 't-sql' tag is relevant but if they are using Jet/ACE (colloquially 'MS Access'), it would be best IMO to use the ALIKE keyword in place of LIKE, otherwise the engine would need to be in ANSI-92 Query Mode or the '%' wildcard characters to function correctly.