Whitespace in varchar field

[pedalpete]

I've just realized that one of my varchar fields which holds musical genres is full of whitespace.

I've looked online and found a few references saying that text fields have whitespace but that varchar fields will strip the whitespace, but this does not seem to be the case.

Strangely, my output looks like the genres are centered in the field (or maybe that is just how it display on output from command line mysql.
Here is what the output looks like (edit- for some reason the centering effect I get in my output gets cleaned up when I submit to the forum).
Is there any way to clean this up?

 
mysql> SELECT genre FROM artists WHERE genre LIKE '%Rock%' LIMIT 0,5;
+-----------------------------------------------------------+
| genre                                                     |
+-----------------------------------------------------------+
|                                       2-step / Acousmatic / Tape music / Classic Rock                                  |
|                                       2-step / Acousmatic / Tape music / Rock                                          |
|                                       2-step / Acoustic / Rock                                                        |
|                                       2-step / Alternative / Classic Rock                                          |
|                                       2-step / Alternative / Classic Rock                                          |
+-----------------------------------------------------------+
5 rows in set (0.04 sec)
 

[Atli]

From the manual

For VARCHAR columns, excess trailing spaces are truncated prior to insertion and a warning is generated, regardless of the SQL mode in use. For CHAR columns, truncation of excess trailing spaces from inserted values is performed silently regardless of the SQL mode.

VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values.

By "excess trailing spaces" they mean spaces that exceed the given maximum length of the field.

To clean that up, you could use the TRIM() function.

[pedalpete]

Thanks Atli,

I didn't realize mysql had a trim function and i was hoping to not do this through PHP.
Unfortunately, the trim function doesn't seem to be working for me.

I've tried both of the following, but still get my response back with spaces.

SELECT CONCAT(  '/ ', TRIM(  '               ' FROM genre ) ,  ' /' ) 
FROM artists
WHERE genre LIKE  '%rock%'
LIMIT 0 , 10
 

or

SELECT CONCAT(  '/ ', TRIM( genre ) ,  ' /' ) 
FROM artists
WHERE genre LIKE  '%rock%'
LIMIT 0 , 10
 

With both of these, i still have about 7 or 8 spaces on each side of my genre.
I've tried with the "BOTH"

[coolsti]

Are you sure that you are actually getting the whitespace from your database? Is it possible that the whitespace is appearing somehow in whatever is presenting the results to you?

Because it looks as if you are doing everything correctly.

Try to do a query where you just spit out the raw genre field as TRIM(genre), perhaps with a non-whitespace symbol concatenated on both sides, for example CONCAT('X',TRIM (genre),'X') to see if the problem is with your query or with your presentation. In the above, do not add any spaces at all, as you do in your query, just to help debug this.