Mysql:Trim all fields in database

Question

UPDATE mytable SET mycolumn= LTRIM(RTRIM(mycolumn));

works fine on trimming columns removing trailer spaces, but how can i adjust it to trim all columns without having to write each column name in table ?? cause i kind have a huge database.

Answer 1

Some years late, but might help others: This code trims all fields of a the table your_table. Could be expanded to work on the whole database in the same way....

SET SESSION group_concat_max_len = 1000000;
SELECT concat('update your_table set ',
    group_concat(concat('`',COLUMN_NAME, '` = trim(`',COLUMN_NAME,'`)')),';')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'your_table'
INTO @trimcmd;
    
PREPARE s1 from @trimcmd;
EXECUTE s1;
DEALLOCATE PREPARE s1;

Answer 2

you expand the query for each column:

UPDATE mytable
SET mycolumn = LTRIM(RTRIM(mycolumn)),
    mycolumn2 = LTRIM(RTRIM(mycolumn2)),
    ...;

Answer 3

Since the question asks for the whole database, here is the script that generates the required SQL. I skip the auto execute, execute it as you like.

-- Set your database name here
SET @my_database:='YOUR_DB_NAME';

SET SESSION group_concat_max_len = 1000000;

SELECT 
    CONCAT('UPDATE `', @my_database, '`.`', TABLE_NAME, 
            '` SET ', GROUP_CONCAT(
                CONCAT('`', COLUMN_NAME, '` = TRIM(`', COLUMN_NAME, '`)')
                ORDER BY ORDINAL_POSITION ASC),
            ';') AS `query`
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = @my_database
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME ASC;

@ZweiStein Thanks.

Answer 4

UPDATE mytable SET 
mycolumn = LTRIM(RTRIM(mycolumn)), 
mycolumn2 = LTRIM(RTRIM(mycolumn2)) 

and so on, and so forth.

Answer 5

If there are not too many columns, you could just
directly UPDATE each by your_column_name, via the TRIM() function:

UPDATE mytable SET 
mycolumn1 = TRIM(mycolumn1), 
mycolumn2 = TRIM(mycolumn2),
mycolumn3 = TRIM(mycolumn3),
mycolumn4 = TRIM(mycolumn4)

Otherwise, ZweiStein's answer above for a single table,
or Izhar Aazmi's answer for an entire database seem the way to go.

Hiram's answer to another SO Post includes a check to only TRIM VARCHAR fields: excellent feature!

Or, if using T-SQL, or others which do not support TRIM, use the LTRIM(RTRIM(...)) trick,
suggested by Jim Rubenstein and Denis de Bernardy above.

Answer 6

I was actually looking for something similar for a legacy table that's constantly updated by an outside source when I came across this question. I realize the OP was looking for a purely SQL(MySQL) answer, but in case you use Rails, you might find this tidbit that I came up with helpful:

MyModel.update_all(MyModel.columns.map(&:name).map{|x| "#{x} = TRIM(#{x})"}.join(', '))

You can also wrap it into a class method in your model

class MyModel < ActiveRecord::Base
  def self.trim_all
   update_all(columns.map(&:name).map{|x| "#{x} = TRIM(#{x})"}.join(', '))
 end
end

Then call it like this

MyModel.trim_all

Answer 7

You can use PHP for it ( in order to avoid sql errors, better print queries then execute them later ) :

$dbHost = 'localhost';
$dbUsername = 'root';
$dbPassword = '';
$dbName = 'database';
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
$db->set_charset("utf8");

$queries = '';
$query="SELECT * from table";
$result = $db->query($query);
$headers = $result->fetch_fields();
foreach($headers as $header) {
        $col = $header->name;

       $queries .= "UPDATE table SET `".$col."` = TRIM(`".$col."`) </br>";
}
echo $queries;

?>