iterate mysql fileds and update fields

Question

Hi,

my data in tables contains some spaces, so I am using this command to trim it

update results set Field1 = trim(Field1);

I am doing this manually for all the fields.

Can anyone provide me with a common sql to automatically trim the total number of fileds in a table?

Thanks,

Answer 1

that's not possible. you will have to update each column. but you can save some time like this:

update results
   set field1 = trim(field1)
     , field2 = trim(field2)

alternatively, you could query the INFORMATION_SCHEMA.COLUMNS table to locate all of the VARCHAR columns and use that to build queries in another language like PHP or Perl.

Answer 2

You can't do it automatically in one statement, but what you can do is this:

SHOW COLUMNS FROM myTable WHERE `Type` LIKE '%char%';

This will give you a list of all the CHAR and VARCHAR fields. Loop through those results, building an SQL statement:

$temp = array();
while ($row = mysql_fetch_assoc($result)) {
    $temp[] = sprintf('`%1$s` = TRIM(`%1$s`)', $row['Field']);
}
$sql = "UPDATE myTable SET " . implode(", ", $temp);
mysql_query($sql);