Q:

organise change field values in mysql

i need to sort through a column in my database, this column is my category structure the data thats in the column is city names but not all the names are the same for each city, what i need to do is go through the values in the column i may have 20-40 value that are the same city but written differently i need a script that can interpret them  and change them to a single value 
so i may have two values in the city column say:( england > london ) and ( westlondon ) but i need to change to just london, is there a script out there that is capable of interpreting the values that are already there and change them to the value  would want i know the dificult way of doing this one by one but wondered if there was a script in any language that could complete this 

A:

I've done this sort of data clean-up plenty of times and I'm afraid I don't know of anything easier than just writing your own fixes.
One thing I can recommend is making the process repeatable. Have a replacement table with something like (rulenum, pattern, new_value). Then, work on a copy of the relevant bits of your table so you can just re-run the whole script.
Then, you can start with the obvious matches (just see what looks plausible) and move to more obscure ones. Eventually you'll have 50 without matches and you can just manually patch entries for this.
Making it repeatable is important because you'll be bound to find mis-matches in your first few attempts.
So, something like (syntax untested):
CREATE TABLE matches (rule_num int PRIMARY KEY, pattern text, new_value text)

CREATE TABLE cityfix AS 
SELECT id, city AS old_city, '' AS new_city, 0 AS match_num FROM locations;

UPDATE c SET c.new_city = m.new_value, c.match_num = m.rule_num 
FROM cityfix AS c JOIN matches m ON c.old_city LIKE m.pattern 
WHERE c.match_num = 0;

-- Review results, add new patterns to rule_num, repeat UPDATE
-- If you need to you can drop table cityfix and repeat it.

