CODEWITHSUNDEEP
mysqlsql
boh
boh

Reputation: 1547

Comparison with trailing spaces in MySQL

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.

Upvotes: 9

Views: 3248

Answers (7)

series0
series0

Reputation: 7

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

(figur eht oot)

Upvotes: -2

Frank
Frank

Reputation: 2043

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.

Upvotes: 3

Salman Arshad
Salman Arshad

Reputation: 272066

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).

Upvotes: 2

1000111
1000111

Reputation: 13519

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.

enter image description here

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)

Upvotes: 8

Palak Nagar
Palak Nagar

Reputation: 52

Try this - 

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

Upvotes: -3

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

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)

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453028

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.

Upvotes: 3

Related Questions