Trailing whitespace in varchar needs to be considered in comparison

user_290379 0
Viewed 5379

I am using a table with a varchar column. I did not realize that trailing whitespace is not considered in comparisons (and that, apparently, two values that differ only in amount of trailing whitespace will violate the uniqueness property, if specified).

I need to fix this in the table, preferably in place. Is there a recommended path to fixing a table like this in MySQL?

I am accessing the DB strictly through a program I control, so switching to a non-human readable format such as binary would be fine. But I am not sure how to do such a thing and don't want to destroy the table.

mysql
5 Answers

I have to assume you're using MySQL 5.x because MySQL 4.x doesn't store trailing spaces in a VARCHAR column.

Using the standard = operator in MySQL, as you indicated, trailing spaces are not considered:

SELECT 'this' = 'this ' returns TRUE

However, LIKE compares the strings character by character, so trailing spaces are significant.

SELECT 'this' LIKE 'this ' returns FALSE.

Both = and LIKE may be case insensitive, using the default collation. Use the COLLATE clause to specify the collation if you need to compare them in a case sensitive manner.

user_1684930

Database collation has a significant role in this. I had a similar situation and figured out that character set = utf8mb4 and collation = utf8mb4_0900_as_cs solves this problem.

You can read more here.

user_17117110

This is ancient, but you can also use the binary keyword.

mysql> select 'hello'='hello  ', binary 'hello'='hello  ';
+-------------------+--------------------------+
| 'hello'='hello  ' | binary 'hello'='hello  ' |
+-------------------+--------------------------+
|                 1 |                        0 |
+-------------------+--------------------------+
1 row in set (0.00 sec)

This will also make the search case sensitive.

mysql> select 'hello'='HELLO', binary 'hello'='HELLO';
+-----------------+------------------------+
| 'hello'='HELLO' | binary 'hello'='HELLO' |
+-----------------+------------------------+
|               1 |                      0 |
+-----------------+------------------------+
user_36177460
user_56795630
Related