KB. KB. - 7 months ago 38
SQL Question

MySQL - how to remove white space in a mysql field

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

Table1

field1 - eg 'Afghanistan' (without quotes)
field2 - eg 'AF'(without quotes)


in some rows the second field seem to have a white space before or/and after which is effecting queries.

Table1

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


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

Answer

You're looking for TRIM.

UPDATE FOO set FIELD2 = TRIM(FIELD2);