Find pattern and replace as the first one

[ExcelTheCell]

Hi people I'm not sure is this possible:

I have one column that contains lets say:

New York234
NewYork-234
NewYork212
L.A kosč
L.Aahsu

I would like to all New York records turn in same name "New York" and all L.A records to "L.A"... I would do that manualy but i have over 300.000 records.. Do you have any idea how could this be done?

 

[jackd]

Does this column have a related column eg, a number to do lookups for example?
Do you have a backup? Once you do the Update you can not undo the action.

Here is the pattern of an update query that should work.

Update TableName
Set ColumnName = "New York"
Where
ColumnName like "NewYork*" or
ColumnName like "New York*"

You would repeat the update for each name pattern you want to change.

If you have a list of city names in a table you could set it up in a loop construct to do much of what you need.

 

[ExcelTheCell]

Well that is the problem that from 300.000 records there are cca 125.000 records of diferent names...


I was thinking something like

Find in colum_name records that begins with same 3letter and replace there names as the first from there group.

Example:

New York234
NewYork-234
NewYork212
L.A kosč
L.Aahsu

It will find this:
New
New
New
L.A
L.A

and replace them with:
New York234
New York234
New York234
L.A kosč
L.A kosč

Don,t worry i have always backup

 

[jackd]

You could use the first 3 letters -- it could introduce an error, but if it's close enough for you then go with it.

New York
Newcastle
New Orleans
New Gratton
New Mexico
New Amsterdam,
NewPort Beach,
New Haven,
New Middletown,
New Carisle,
New Chicago,
New Harmony,
New Market,
New Palestine,
New Paris,
New Perkin,
New Providence,
New Richmond,
New Ross,
New Whiteland,
New Plymouth,
New Westminster,
etc could all become New York

Just an example. You know the significance of error better than we do.