Mysql TRIM not working

Question

I'm trying to cleanse my numbers in my database by using :

update valuations set Telephone = TRIM(Telephone) where  1 = 1

but 0 rows are effected and all the nunmbers still have a space in them. The datatype is varchar so im unsure why this isn't working - can anyone help?

Thanks

Answer 1

As I said in my comment, I'm guessing your spaces are in the middle of your entries, TRIM won't work then. Use REPLACE instead :

UPDATE valuations SET Telephone = REPLACE(Telephone, ' ', '') WHERE 1 = 1;

Answer 2

trim() will remove only those spaces at the ends of a string. If you want to remove those inside the string, use replace:

UPDATE valuations SET Telephone = REPLACE(Telephone, ' ', '') where  1 = 1