Triming leading and trailing spaces of a all values in a table column?

[Ken Masters]

Hi,

Is there an sql statement that will allow me to trim all leading and trailing spaces of all values in a table column?

Cheers,

Iain

[PhilipToop]

UPDATE table set colname = TRIM(colname);

[disgracian]

What database are you using?

Cheers,
D.

[Ken Masters]

I'm using mysql 5.

Seems... UPDATE table set colname = TRIM(colname);

Isn't the right syntax. I'm doing the sql statement in phpmyadmin.

[SJH]

That is the correct syntax for MySQL. What error did it give you?

[PhilipToop]

Update tablename set colname = TRIM(colname);

is the right syntax.

colname is the name of the column whose values you wish to remove leading and trailing spaces from and tablename is the name of the table in which the column resides.

[Ken Masters]

Thanks Philip, I must have written it incorrectly but it works now... although no rows were affected, which is strange because I can see leading spaces in the majority of my rows in that column. I'm not sure how to get rid of them besides manually removing them.

[SJH]

I had a similar problem in the past - read the thread I posted for the solution

[disgracian]

I'm using mysql 5.

Then why didn't you post this question in the MySQL section?

Mods, can we have this moved?

Cheers,
D.

[PhilipToop]

You could use the following

SELECT * from tablename where colname like ' %' or colname like '% ';

to see if any columns with leading or trailing spaces are reported.

I am wondering if the character is not a space but is being seen as a space.