MySQL remove all whitespaces from the entire column

by

To replace all spaces :

UPDATE `table` SET `col_name` = REPLACE(`col_name`, ' ', '')

To remove all tabs characters :

UPDATE `table` SET `col_name` = REPLACE(`col_name`, '\t', '' )

To remove all new line characters :

UPDATE `table` SET `col_name` = REPLACE(`col_name`, '\n', '')

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

To remove first and last space(s) of column :

UPDATE `table` SET `col_name` = TRIM(`col_name`)

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim

and if you want remove all type of spaces, you can combine all of this functions :

UPDATE `table` SET `col_name` = REPLACE(REPLACE(REPLACE(`col_name`, ' ', ''), '\t', ''), '\n', '');

More Related Contents:

  1. Create a Cumulative Sum Column in MySQL
  2. Equivalent of explode() to work with strings in MySQL
  3. 1064 error in CREATE TABLE … TYPE=MYISAM
  4. mysql select dynamic row values as column names, another column as value
  5. Access denied for user ‘root’@’localhost’ (using password: YES) (Mysql::Error)
  6. MySQL DELETE FROM with subquery as condition
  7. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  8. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  9. Simple Random Samples from a Sql database
  10. node.js mysql error: ECONNREFUSED
  11. Relational database design multiple user types
  12. Checking multiple columns for one value
  13. What is better in MYSQL count(*) or count(1)?
  14. #1273 – Unknown collation: ‘utf8mb4_unicode_ci’ cPanel
  15. Not unique table/alias
  16. MySQL GROUP BY DateTime +/- 3 seconds
  17. MySQL foreign key to allow NULL?
  18. Dynamic conversion of string into column name. MySQL
  19. How to write a (MySQL) “LIMIT” in SQL Server?
  20. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  21. MySQL 5.0 indexes – Unique vs Non Unique
  22. MySQL – Select from a list of numbers those without a counterpart in the id field of a table
  23. sql group by versus distinct
  24. Accent insensitive search query in MySQL
  25. Updating table in trigger after update on the same table
  26. The web application [] appears to have started a thread named [Abandoned connection cleanup thread] com.mysql.jdbc.AbandonedConnectionCleanupThread
  27. MySQL: Querying for unicode entities
  28. undefined method `eq’ for nil:NilClass with rails 3 and ruby enterprise on ubuntu hardy
  29. How do I extract Month and Year in a MySQL date and compare them?
  30. INSERT INTO fails with node-mysql

Leave a Comment