Changing "St" to "Saint"

[John T-R]

Hi,
I have a large table in which there are many streetnames with "St" as an abbreviation for "Saint". I would like to construct a query that changes all the "St" to "Saint". How do I do this without replacing the rest of the streetname? ie St George's Way needs to be Saint George's Way etc etc. I could construct a query to replace all St with Saint but I would lose the rest of the streetname.
All suggestions appreciated!
Cheers,
J

 

[ejstefl]

Use the replace function.

Replace([address],"St","Saint")

Be careful though - are there any cases where St could be an abbrevation for street?

 

[Pat Hartman]

I think the suggested code will have the undesirable effect of changing the St abbreviation for street to saint also.

ALWAYS back up before doing something like this.

 

[mresann]

Do a SELECT query based on this filter:

trim(left([Addres],3) = "ST ")) OR trim(left([Addres],3) = "ST."))

In the first condition, notice that ST is followed by a space.

This should identify all the St prefixes, including those with or without the period, in the first three characters of the field. You should be able to whittle the number of records down to see if you can readily update them without compromising the ST in Street.

If you are satisfied with this query, change it from SELECT to UPDATE. Do as Pat says, though, and back up the data.

 

[Pat Hartman]

If the column contains the entire street address, you'll need to get rid of the leading house numbers before you can expand St to Saint also.

 

[neileg]

I don't know where you are in the world, but in the UK, the officially accepted name of the street may well be St Georges Way and not Saint George's Way

The vagaries of spelling and punctuation in street and place names is bewildering. The UK has never undertaken a standardisation exercise in the way that the US did (in the 19th cent?)