CODEWITHSUNDEEP
phpmysql
Gaz Smith
Gaz Smith

Reputation: 1108

Mysql TRIM not working

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

Upvotes: 6

Views: 3178

Answers (4)

Mike B
Mike B

Reputation: 2776

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

Upvotes: 3

aslawin
aslawin

Reputation: 1981

You should first trim string and after that replace space with empty character:

update valuations set Telephone = REPLACE(TRIM(Telephone), ' ', '') where  1 = 1

TRIM function is useful when you need to remove other blank characters from begining and end of string, for example new line character or TAB.

More about TRIM() function: http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_trim

Upvotes: 2

Alex Banerjee
Alex Banerjee

Reputation: 474

Hi pandemic please use

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

Hope this works pal

Upvotes: 2

roberto06
roberto06

Reputation: 3864

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;

Upvotes: 6

Related Questions