mysql: remove text from column that is contained in another column

So ihave a database that contains address, city, state, and zipcode columns. The problem is the ad开发者_如何学Cdress column includes the address as well as the city, state, and zip. As these are already columns, i dont want them in the address column. Is there a way to dynamically remove the data from the address column that matches city, state, and zip? The database has over 50,000 rows.

Possible with REPLACE, but messy to do: try this at the MySQL console:

mysql> create table `m2` (`id` int(11) auto_increment, a TEXT, b TEXT, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into `m2` (a, b) VALUES ('200 Honolulu Drive Los Angeles CA 89999','89999');
Query OK, 1 row affected (0.01 sec)

mysql> select * from m2;
+----+-----------------------------------------+-------+
| id | a                                       | b     |
+----+-----------------------------------------+-------+
|  1 | 200 Honolulu Drive Los Angeles CA 89999 | 89999 |
+----+-----------------------------------------+-------+
1 row in set (0.00 sec)

mysql> update `m2` set a = TRIM(REPLACE(a, b,'')) WHERE id = 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from m2;
+----+-----------------------------------+-------+
| id | a                                 | b     |
+----+-----------------------------------+-------+
|  1 | 200 Honolulu Drive Los Angeles CA | 89999 |
+----+-----------------------------------+-------+
1 row in set (0.00 sec)

If your formats aren't VERY regular (or you have the zip code, state or city value that occurs in multiple places in the address field) you could easily damage your address field so I'd recommend you do it with code, rather than SQL.

But it is there for you, in a pinch.