CODEWITHSUNDEEP
mysqlsql
Peter Howe
Peter Howe

Reputation: 1403

How to string-compare for a single space

I want to check whether a column has any values that are a single space character.

I initially thought that

WHERE my_column = ' '

would be sensible. But no. That will also match columns which have multiple spaces for some reason:

SELECT '    ' = ' '           => true

So I can use a regular express or hex encoding to test:

WHERE HEX(my_column) = '20'
WHERE my_column REGEXP '^\ $'

Both work. But I suspect both (certainly the latter) will be quite inefficient.

Is there a better way?

Upvotes: 8

Views: 1091

Answers (3)

Michael Berkowski
Michael Berkowski

Reputation: 270617

A BINARY comparison of the two strings is required for an exact match

Under normal circumstances, trailing whitespace is not regarded in the comparison, but the BINARY operator forces it to be:

BINARY also causes trailing spaces to be significant.

mysql> SELECT BINARY '   ' = ' ';
+--------------------+
| BINARY '   ' = ' ' |
+--------------------+
|                  0 |
+--------------------+

Incidentally, it isn't just whitespace-only comparisons that are affected by the trailing whitespace issue:

mysql> SELECT 'abc   ' = 'abc';
+------------------+
| 'abc   ' = 'abc' |
+------------------+
|                1 |
+------------------+

...but...

mysql> SELECT BINARY 'abc   ' = 'abc';
+-------------------------+
| BINARY 'abc   ' = 'abc' |
+-------------------------+
|                       0 |
+-------------------------+

...and even more confusingly, leading whitespace is significant:

mysql> SELECT ' abc   ' = 'abc';
+-------------------+
| ' abc   ' = 'abc' |
+-------------------+
|                 0 |
+-------------------+

Regarding indexing:

BINARY will prevent an index from being used on the character column. However, a note on the docs suggests that the index will be used if the BINARY operator is applied to the string literal side of the comparison as in:

SELECT * FROM `tbl` WHERE `col` = BINARY 'string   '

Upvotes: 12

Greg Oks
Greg Oks

Reputation: 2730

where my_column = ' ' and LENGTH(my_column) = 1

Upvotes: 3

Vardan Gupta
Vardan Gupta

Reputation: 3585

SELECT CASE WHEN COL_NAME='' THEN 'yes' ELSE 'no' END AS SPACE_INDICATOR FROM Table_NAME WHERE LENGTH(COL_NAME)=1;

Upvotes: 0

Related Questions