Have a MYSQL database. Anyone know how to go through all the data fields in the database looking for a certain character, such as a ";" and replace it with a blank and then put it back into the database?

UPDATE table SET column = REPLACE(column,";"," ")

Originally Posted by chrishirst

UPDATE table SET column = REPLACE(column,";"," ")

Ah, but how do you know which table and which column?

In SQL Server ( Microsoft ), you can use one query ( Select name From sysObjects Where xType = 'u' -- u as in user table ) to get a list of all the tables in the database, and another ( Select name From sysColumns ) to get a list of columns. You would do this as an iteration: for each row in sysObjects, you also want it's ID, and use that in the where clause against sysColumns to get the columns for that particular table. And for each of those, you would fire off the above update query. Which means potentially thousands of update commands.

No idea how this would be accomplished in MySql, but I'm guessing it's similarly easy.

Originally Posted by ForrestCroce

Ah, but how do you know which table and which column?

In the query "UPDATE table SET column = REPLACE(column, ";"," ")"
table is the name of the table you want to update and column is the name of the column in that table. Let's say I had a table called users and wanted to change entries in the column called address, the query would be: -

UPDATE users SET address = REPLACE(address, ";", " ")

Moving on, it is just as easy (if not easier) to get a list of tables from a given database with the following query:

SHOW TABLES FROM db_name;

and a list of columns from a given table:

SHOW COLUMNS FROM tbl_name;

Likewise, one could iterate throughout and perform multiple updates if required

Hope this adds something for someone
Ian