Replace Function Based on Beginning of Field

[Dordee]

I have a large database of city names, and I need to replace many abbreviations and punctuation with spaces and/or spell out the name. Using Find and Replace is too time consuming and crashes Access because there are so many records so I was thinking of doing it with an Update Table query using a REPLACE function.

One problem I am having is that I want the string that it is looking for to only change if it matches the beginning of the field.

For example, if "ST " starts at the beginning of the field, I want to replace it with "SAINT ".


However, if "ST " is anywhere else - for example "FIRST N", I don't want it changed.

It seems really simple, as I can query this, and do it using Find and Replace, but I can't find how to do it with the REPLACE function. I even looked into a little doing it in the SQL view in Access.

Any ideas?

Thank you!

[CJ_London]

Think you will have many more issues so should try to determine as many of them as possible before deriving a solution but with regards the one you mention try

newstring=replace(left(oldstring,3),"ST ","SAINT ") & mid(oldstring,4)

[Dordee]

Thank you so much! I do have many to change, but only one or two in which the string must match the first positions. The rest I can handle with a space before or after. I will give this a try - thanks again!