remove trailing whitespace in MySQL

Question

Why does my SELECT not ignore whitespace, and why does TRIM seem to have no effect?

Here are the partial results of something like SELECT DISTINCT crop_year from raw_data:

Rows    crop_year
105755  '2010'
12326   '2010 '
256363  '2011'
319321  '2011 '
...

I typed in the single quotes to illustrate the fact that there is trailing whitespace.

I have 2 problems here on my production server... which I cannot replicate locally:

A. I expect SELECT to ignore whitespace, as explained in numerous other questions, as well as the MySQL Docs, but it clearly does not work this way on my prod server.

B. I expect UPDATE raw_data SET crop_year = TRIM(crop_year) to fix the problem, but running this query results in 0 affected rows.

Other background: This column type is VARCHAR(11). If it's relevant, the table contains mixed storage engines and collations: this table is MyISAM, and this column is currently latin1_swedish_ci.

PS: In this specific case, year is always a 4 digit value, so I eventually changed the column from a VARCHAR(11) to CHAR(4)... which effectively trimmed the whitespace, but I am still posting the question because I find it likely that I will encounter a similar problem on other columns which are not of a fixed length.

Community · Accepted Answer · 2017-05-23 12:12:55Z

1

Figured it out, with help from this question. TRIM does not respect all whitespace... just spaces. In my case, I needed:

select distinct trim(BOTH '\r' from crop_year) as crop_year FROM raw_field_data

Other variations could include

select distinct trim(BOTH '\n' from crop_year) as crop_year FROM raw_field_data or select distinct trim(BOTH '\n\r' from crop_year) as crop_year FROM raw_field_data

edited May 23, 2017 at 12:12

CommunityBot

11 silver badge

answered Mar 24, 2015 at 1:32

Ryan

6,3192 gold badges27 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

huddreth · Accepted Answer · 2015-03-24 01:35:03Z

0

Maybe you place extra space in your "form" when you enter the value on the crop year. check your input forms maybe...

answered Mar 24, 2015 at 1:35

huddreth

3231 gold badge3 silver badges5 bronze badges

Collectives™ on Stack Overflow

remove trailing whitespace in MySQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related