Menu
We will be using the replace() function as replace() function will remove the white spaces from between, start, and the end of the string value. Name of the table. Name of the column whose values are to be updated. The characters to be removed from each value.
You can remove special characters from a database field using REPLACE() function. The special characters are double quotes (“ “), Number sign (#), dollar sign($), percent (%) etc.
SQL Server TRIM() Function The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string. Note: Also look at the LTRIM() and RTRIM() functions.
The TRIM() function removes leading and trailing spaces from a string.
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
Since the question is how to replace ALL whitespaces
UPDATE `table`
SET `col_name` = REPLACE
(REPLACE(REPLACE(`col_name`, ' ', ''), '\t', ''), '\n', '');
Working Query:
SELECT replace(col_name , ' ','') FROM table_name;
While this doesn't :
SELECT trim(col_name) FROM table_name;
Using below query you can remove leading and trailing whitespace in a MySQL.
UPDATE `table_name`
SET `col_name` = TRIM(`col_name`);
Just use the following sql, you are done:
SELECT replace(CustomerName,' ', '') FROM Customers;
you can test this sample over here: W3School
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With