The Core

I have some weird stuff going on in my data imports. I find myself getting some trailnig white space, which then messes up my URLs as I base them on two field, category and subcategory.

I hjave various routines to clean them, eg

//Clean category
mysql_query("UPDATE main2006 SET category=replace(category, '\(', '')");
mysql_query("UPDATE main2006 SET category=replace(category, '\)', '')");
mysql_query("UPDATE main2006 SET category=replace(category, '&', 'and')");
mysql_query("UPDATE main2006 SET category=replace(category, '\/', ' ')");
mysql_query("UPDATE main2006 SET category=replace(category, '\'', '')");
mysql_query("UPDATE main2006 SET category=replace(category, '\.', ' ')");
mysql_query("UPDATE main2006 SET category=replace(category, '\(', '')");
mysql_query("UPDATE main2006 SET category=replace(category, '\)', '')");
mysql_query("UPDATE main2006 SET category=replace(category, '\'', '')");
mysql_query("UPDATE main2006 SET category=replace(category, '&', 'and')");
mysql_query("UPDATE main2006 SET category=replace(category, '+', '')");
mysql_query("UPDATE main2006 SET category=replace(category, '?', '')");
mysql_query("UPDATE main2006 SET category=replace(category, '-', ' ')");
Sometime I get spaces at the end of the field.

Is there a query or method to remove white space, if it present at the end of a field?

Or do I have to strip them out oe by one with php, remove and then insert back in?

Any ideas.

Cheers

Wow, next page, i found the answer. For some reason, VARCHAR's do not remove the trailing padding in mysql5 but CHARs do.

Nutty

With MySQL 5.0 however things changed so now VARCHAR keeps trailing spaces while CHAR columns do not any more. Well in reality CHAR columns are padded to full length with spaces but it is invisible as those trailing spaces are removed upon retrieval. This is something you need to watch both upgrading to MySQL 5.0 as well as designing your applications – you should keep into account if you mind trailing spaces stored choosing VARCHAR vs CHAR in addition to fixed length vs dynamic level rows and space spent for column size counter.

http://www.mysqlperformanceblog.com/2006/11/27/trailing-spaces-in-mysql/