jkushner jkushner - 4 years ago 154
SQL Question

Remove whitespace in mysql

I have the following data (this is a slice of 4000 records):

--------------------------------+
| google_location_hours |
+---------------------------------------------------------------------------------------------------------+
| 11.30 AM - 5 AM,11.30 AM - 5 AM,11.30 AM - 5 AM,11.30 AM - 5 AM,11.30 AM - 5 AM,2 PM - 5 AM,4 PM - 5 AM |
+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


If you notice, the data looks like this
11.30 AM - 5 AM


I need to replace it with this
11.30AM-5AM
. This goes for every comma separated data in this record (and then applied on several thousand records).

Not quite sure how to handle this.

Answer Source

MySQL's replace function seems to hit the mark:

SELECT REPLACE(google_location_hours, ' ', '')
FROM   mytable

EDIT:

If you wish to replace the actual data in the table, the same function could be used with an update statement:

UPDATE mytable
SET    google_location_hours = REPLACE(google_location_hours, ' ', '')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download