Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I recently had an issue come up where I noticed that MySQL would ignore trailing whitespace when issuing a GROUP BY. Here's the table:

CREATE TABLE `myTable` (
 `id` int(10) unsigned NOT NULL,
 `foo` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

And here's an example query I ran (not a production use, but shows what I'm trying to fix):

SELECT foo, CHAR_LENGTH(foo) FROM myTable
WHERE foo='bar'
GROUP BY foo

Which would return a result set:

| foo  | CHAR_LENGTH(foo)  |
+------+-------------------+
| Bar  |                 4 |

All well and good, except 'Bar' is length three, not four. So I went to examing the individual entries (No group by):

SELECT foo, CHAR_LENGTH(foo) FROM myTable
WHERE foo='bar'

| foo  | CHAR_LENGTH(foo)  |
+------+-------------------+
| Bar  |                 3 |
| Bar  |                 3 |
| Bar  |                 3 |
| Bar  |                 4 |
| Bar  |                 3 |

Now, there are five entries for Bar, four of which correctly report a length 3. But there's one that reports a length of four.

It turns out the one with length four has a space on the end: "Bar ". I would have expected GROUP BY to treat this as a completely different value and not lump it with the other "Bar"s.

Is there a way to tell MySQL to consider values different if they have trailing (or leading) whitespace?

(running mysql 5.1.55 64-bit on mac osx)

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

char values are space-padded when they are stored, so all trailing whitespace is assumed to be padding and removed when retrieving values. varchar fields will retain trailing whitespace, but by default it is removed for comparisons. (see The CHAR and VARCHAR types)

To retain the trailing whitespace for varchar values, use the binary operator. Something like SELECT foo, CHAR_LENGTH(foo) FROM myTable WHERE foo='bar' GROUP BY BINARY foo should do what you want.

share|improve this answer
    
that's exactly what I needed. worked great –  Derek Downey Mar 8 '11 at 13:10
add comment

The GROUP BY clause can be multiple fields and/or calculated fields:

SELECT foo, CHAR_LENGTH(foo) FROM myTable
WHERE foo='bar'
GROUP BY foo,CHAR_LENGTH(foo)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.