Mysql str replace whitespace issue

Question

|      382 | Ongoing     |
|      383 | Ongoing     |
|      384 | Ongoing     |
|      385 | Ongoing     |
|      386 | 
Ongoing
   |
|      387 | 
Ongoing
   |
|      388 | 
Ongoing
   |
|      389 | 
Ongoing
   |
|      390 | 
Ongoing
   |
|      391 | 
Ongoing

Above is my sql, somehow or somewhat I did some mistake when insert the value.

I wanna do a str replace to replace the empty "spaces"

I did the following

update content set status = replace(status,' ','');

It replace 19 rows, then I tried again, it replace 0 rows. but the empty spaces still exist.

How do I solve this by str_replace at mysql

Thanks!

possible duplicate of [MySQL - how to remove white space in a mysql field](http://stackoverflow.com/questions/6858143/mysql-how-to-remove-white-space-in-a-mysql-field) — Muhammet Arslan, Aug 27 '15 at 19:03

score 1 · Answer 1 · answered Aug 27 '15 at 19:02

1

You're looking for TRIM.

UPDATE FOO set FIELD2 = TRIM(FIELD2);

answered Aug 27 '15 at 19:02

Muhammet Arslan

975
1
9
33

I tried this, it doesn't solve the issue. The white space is still not removed .. – Manga Black Aug 27 '15 at 19:04
Maybe your spaces is not whitespaces try to replace \t character. – Muhammet Arslan Aug 27 '15 at 19:07
UPDATE FOO set FIELD2 = TRIM(Replace(Replace(Replace(FIELD2,'\t',''),'\n',''),'\r','')); – Muhammet Arslan Aug 27 '15 at 19:07

score 0 · Answer 2 · answered Aug 27 '15 at 19:06

0

Found it..

UPDATE content set status = TRIM(Replace(Replace(Replace(status,'\t',''),'\n',''),'\r',''));

answered Aug 27 '15 at 19:06

Manga Black

197
2
9

Mysql str replace whitespace issue

2 Answers2