??? LIKE wildcards and/or REGEX ???

[ppetree]

Problem: I have a zip_code/city/state table that has full names and abbreviations for some common things like FT (FORT) and IS (ISLAND).

So, I may have a record something like this:
zip = '31522'
city = 'IS HARBOR POINTE'
locationtext = 'Is Harbor Pointe'
location = 'US-GA-IS HARBOR POINTE'

and another like this:
zip = '02647'
city = 'HYANNIS PORT'
locationtext = 'Hyannis Port'
location = 'US-MA-HYANNIS PORT'

In the above case, using this query works for the first one but screws up the second:

UPDATE zip_bak SET city=(REPLACE (city,'IS ','ISLAND ')),
locationtext=(REPLACE (locationtext,'Is ','Island ')),
location=(REPLACE (location,'IS ','ISLAND '))

Question: Since I have no idea where the search strings appear in a name(beginning, middle, end), I know they are whole words. So how do I change my query to do this right?

I have a whole slew of abbreviations:
N/NO - North
S/SO - South
E - East
W - West
BCH - BEACH
BND - BEND
BRG - BRIDGE
CTY - CITY
DT - DEPOT
FT - FORT
GRV - GROVE
HBR - HARBOR
IS - ISLAND
LK - LAKE
LND - LAND
PK - PARK
PT - POINT
SPR/SPGS/SPNGS - SPRINGS
TWP/TOWNSHP - TOWNSHIP
TN/TWN - TOWN
VLE - VILLE

[smantscheff]

MySQL does not have a regex replacement function. Either you code an internal routine, or you use an external tool capable of regex replacements. I would use PHP and the preg_replace function, or export the whole database to a text editor like EditPad and replace it there, or - if you want to automate it - use sed on the export file for string replacements.