KB. KB. - 4 months ago 14
MySQL Question

How to remove leading and trailing whitespace in a MySQL field?

I have a table with two fields (countries and ISO codes):

Table1

field1 - e.g. 'Afghanistan' (without quotes)
field2 - e.g. 'AF'(without quotes)


In some rows the second field has whitespace at the start and/or end, which is affecting queries.

Table1

field1 - e.g. 'Afghanistan' (without quotes)
field2 - e.g. ' AF' (without quotes but with that space in front)


Is there a way (in SQL) to go through the table and find/replace the whitespace in field2?

Answer

You're looking for TRIM.

UPDATE FOO set FIELD2 = TRIM(FIELD2);
Comments