remove whitespace in between Column values in mySQL

Question

How to remove all spaces between a column field?. The spaces occur in the middle of the text so trim won't work and also replace is not working. my code is

      UPDATE  temp_emp t1,  master_employee t2
      SET t1.lm= t2.emp_id
      where REPLACE(t1.lm, ' ', '') = REPLACE(CONCAT(t2.first_name,'',t2.last_name), ' ', '');

for example when i run the query ,

       select REPLACE(lm, ' ', '') AS concat from temp_emp1

i get the output as follows

         concat
         ----------------------------------------
         rick joe
         james cole
         albert Th

i want the output to be ;like this

          concat
         ----------------------------------------
         rickjoe
         jamescole
         albertTh

http://stackoverflow.com/questions/6940646/mysql-how-to-remove-double-or-more-spaces-from-a-string — mohan111, Jan 03 '17 at 05:09
You don't have to create an own function since MySQL's REPLACE() replaces all the occurrences of a substring. Above example should work if the whitespace character is actually a space. Here's a fiddle: http://sqlfiddle.com/#!9/3bc390/2 — Jari Jokinen, Jan 03 '17 at 05:17

score 0 · Answer 1 · answered May 09 '17 at 21:45

Without knowing the table structures and data, it is difficult for me to follow what you are doing. However, to accomplish the ouput of two concatenated columns is very straightforward.

Assume you have a table master_employee with just two columns and you want to output the FIRST and LAST names concatenated with no spaces in between. You simply use the function concat()for MySQL:

SELECT CONCAT(first_name, last_name)
   from master_employee;

In Oracle, the concatenation is two pipes (||):

SELECT first_name || last_name
   from master_employee;

Hope this helps.

score 0 · Answer 2 · answered Oct 17 '18 at 13:13

If you want to update the existing column which has multiple spaces into one then this update query will be helpful:

UPDATE your_table SET column_that_you_want_to_change= REGEXP_REPLACE(column_that_you_want_to_change, '[[:space:]]+', ' ');

If you don't want any spaces then this should work:

UPDATE your_table SET column_that_you_want_to_change= REGEXP_REPLACE(column_that_you_want_to_change, '[[:space:]]+', '');

remove whitespace in between Column values in mySQL

2 Answers2