Search code examples
mysqltrim

Mysql:Trim all fields in database

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.

Solution

  • 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;