Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Trailing whitespace in varchar needs to be considered in comparison

Tags:

mysql

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.

like image 336
muckabout Avatar asked Sep 08 '10 14:09

muckabout


People also ask

Does varchar store trailing spaces?

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

What does trailing whitespace mean?

Trailing whitespace. Description: Used when there is whitespace between the end of a line and the newline.

Does varchar include spaces?

VARCHAR values aren't padded with extra characters. When CHAR values are retrieved, all trailing spaces are removed. Spaces are retained during the retrieval of VARCHAR values.

Does SQL Server ignore trailing spaces?

Takeaway: According to SQL Server, an identifier with trailing spaces is considered equivalent to the same identifier with those spaces removed.


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

like image 133
Marcus Adams Avatar answered Oct 22 '22 20:10

Marcus Adams