Comparison with trailing spaces in MySQL

Question

This SQL query:

select c1 from table where c1='';

returns rows that have c1=' ' (one empty space) in MySQL.

Is this intended or a bug?

EDIT: please check SQL Fiddle link here, and the number of spaces in SELECT query doesn't matter.

Answer 1

It's all stated there in the documentation. I've quoted the important points here. But I would suggest to go through the full documentation

VARCHAR values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL.

On the other hand, CHAR values are padded when they are stored but trailing spaces are ignored when retrieved.

All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces. “Comparison” in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant.

Explanation: Trailing spaces are ignored while comparing strings using comparison operator ('='). But trailing spaces are significant for LIKE (pattern matching operator)

Answer 2

If your column is from type CHAR and not VARCHAR, than this is correct. On CHAR-Fields will trailing blanks on comparing ignored! So

field = ''
field = '    '

are the same.

Answer 3

This is documented behaviour.

The MySQL documentation for LIKE mentions

trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator:

SQL Server works the same way.

Answer 4

This behavior is in accordance with ANSI SQL-92 standard. Any database conforming to this standard will exhibit same behavior. Quote:

3) The comparison of two character strings is determined as fol-
   lows:

   a) If the length in characters of X is not equal to the length
     in characters of Y, then the shorter string is effectively
     replaced, for the purposes of comparison, with a copy of
     itself that has been extended to the length of the longer
     string by concatenation on the right of one or more pad char-
     acters, where the pad character is chosen based on CS. If
     CS has the NO PAD attribute, then the pad character is an
     implementation-dependent character different from any char-
     acter in the character set of X and Y that collates less
     than any string under CS. Otherwise, the pad character is a
     <space>.

   b) The result of the comparison of X and Y is given by the col-
     lating sequence CS.

So, according to these specs 'abc' = 'abc ' and '' = ' ' evaluate to true (but '' = '\t' is false).

Answer 5

If c1 is CHAR(1), then this is correct, as CHAR columns are fixed width and will be filled with blanks if necessary.

So even if you put '' into a CHAR(1) field you will get ' ' upon SELECTing. Also, filtering for an empty string will yield ' '.

Please accept Martin Smith's answer, as he gave the correct hint before me.

Also, as per MySQL documentation, trailing whitespace is ignored when comparing strings with =, so if your c1 column contains only spaces (or one in your case), it will be returned even though you filter WHERE c1 = '':

In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator

mysql> SELECT 'a' = 'a ', 'a' LIKE 'a ';
+------------+---------------+
| 'a' = 'a ' | 'a' LIKE 'a ' |
+------------+---------------+
|          1 |             0 |
+------------+---------------+
1 row in set (0.00 sec)

Answer 6

select c1, length(c1) as l
from table t_name 
group by l

(figur eht oot)

Answer 7

Try this -

Select case when c1 = '' then ' ' else c1 end from table ;