Remove space from concatenated string fields in MySQL

Question

I have 3 fields that I concatenate and it works very well in my query, but I cannot decide how to remove the space from the merged data in the concat field.

TRIM(CONCAT(c.data1,c.data2)) AS concat_done 

Result:

 concat_done 33 0250S 0450E 028NW 

Answer 1

instead

 TRIM(CONCAT(c.data1,c.data2)) AS concat_done 

try

 REPLACE(CONCAT(c.data1,c.data2), ' ', '') AS concat_done 

Answer 2

add REPLACE call:

 REPLACE(TRIM(etc...), ' ', '') ^--one space ^-- no spaces 

Answer 3

First of all, you should probably show your input as well as your output.

Secondly, trim () removes leading and trailing spaces , so you want concat(trim(var1), trim(var2)) instead of trimming the concatenated version, which now has internal spaces.

update: Or, as other answers say, just use replace (). But that’s why trim () didn’t work the way you wanted.

Answer 4

UPDATE Table_1 set Column_1 = TRIM (Replace (replace (replace) (Column_1, '\ t', ''), '\ n', ''), '\ r', ''));

You can also use a large replacement in an internal replacement.