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.
-
Replace is not a SQL function. If you're executing your SQL in Access itself, then you can use the VBA Replace() function in your SQL, because the Access expression service allows the use of VBA predefined and user-defined functions in SQL.David-W-Fenton– David-W-Fenton2009-02-14 02:05:04 +00:00Commented Feb 14, 2009 at 2:05
-
@David W. Fenton: again you fail to see the difference between Access and Jet/ACE. REPLACE() can indeed be used in ACE/Jet SQL outside of the Access UI. If the poster is using MS SQL Server, REPLACE is also a keyword in T-SQL (and SQL-92).onedaywhen– onedaywhen2009-02-16 09:33:14 +00:00Commented Feb 16, 2009 at 9:33
Add a comment
|
4 Answers
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.
1 Comment
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.
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.
1 Comment
BIBD
Yes... you don't want to make clbuttic mistake.
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.
Comments
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.
1 Comment
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.