Wednesday, July 8, 2015

How to remove tabs at start and end of varchar field in MySQL?

The trim() function only removes the leading and trailing spaces by default. However, you can specify the optional character to trim.

To remove all tabs and spaces in a string:

UPDATE mytable SET email = REPLACE(TRIM(email), '\t', '');
UPDATE mytable SET email = REPLACE(TRIM(email), CHAR(9), '')

To remove tabs and spaces from leading and trailing:

UPDATE mytable SET email = TRIM(CHAR(9) FROM TRIM(email));

Reference:

http://stackoverflow.com/questions/13979169/how-to-remove-tabs-at-start-and-end-of-varchar-field-in-mysql
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim

No comments: