469,127 Members | 1,364 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,127 developers. It's quick & easy.

Whitespace in varchar field

110 100+
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?

Expand|Select|Wrap|Line Numbers
  1.  
  2. mysql> SELECT genre FROM artists WHERE genre LIKE '%Rock%' LIMIT 0,5;
  3. +-----------------------------------------------------------+
  4. | genre                                                     |
  5. +-----------------------------------------------------------+
  6. |                                       2-step / Acousmatic / Tape music / Classic Rock                                  |
  7. |                                       2-step / Acousmatic / Tape music / Rock                                          |
  8. |                                       2-step / Acoustic / Rock                                                        |
  9. |                                       2-step / Alternative / Classic Rock                                          |
  10. |                                       2-step / Alternative / Classic Rock                                          |
  11. +-----------------------------------------------------------+
  12. 5 rows in set (0.04 sec)
  13.  
Sep 23 '08 #1
3 7177
Atli
5,058 Expert 4TB
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.
Sep 23 '08 #2
pedalpete
110 100+
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT CONCAT(  '/ ', TRIM(  '               ' FROM genre ) ,  ' /' ) 
  2. FROM artists
  3. WHERE genre LIKE  '%rock%'
  4. LIMIT 0 , 10
  5.  
or
Expand|Select|Wrap|Line Numbers
  1. SELECT CONCAT(  '/ ', TRIM( genre ) ,  ' /' ) 
  2. FROM artists
  3. WHERE genre LIKE  '%rock%'
  4. LIMIT 0 , 10
  5.  
With both of these, i still have about 7 or 8 spaces on each side of my genre.
I've tried with the "BOTH"
Sep 23 '08 #3
coolsti
310 100+
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.
Sep 25 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Bill | last post: by
4 posts views Thread by Dwayne Epps | last post: by
reply views Thread by sfh | last post: by
10 posts views Thread by Techie | last post: by
7 posts views Thread by James o'konnor | last post: by
10 posts views Thread by Mason Barge | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.