7

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)

2 Answers 2

11

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.

2
  • that's exactly what I needed. worked great Mar 8, 2011 at 13:10
  • This points us in the right direction, but the query you provided doesn't work. Note that expression #1 in your SELECT list is not in GROUP BY clause and contains nonaggregated column foo which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by. If you add Binary to foo, then it would work but the results will not be in string form!
    – Tony Sepia
    Jan 21, 2018 at 16:41
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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