Remove whitespace in mysql

Question

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.

Possible duplicate of [Find and Replace text in all table using mysql query](http://stackoverflow.com/questions/11839060/find-and-replace-text-in-all-table-using-mysql-query) — wogsland, Dec 28 '15 at 19:02

Mureinik · Accepted Answer · 2015-12-28T19:04:36.137

2

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, ' ', '')

edited Dec 28 '15 at 19:04

answered Dec 28 '15 at 19:02

Mureinik

297,002
52
306
350

This looks like what I need however your doing a select and i need a straight replacement – somejkuser Dec 28 '15 at 19:03
@jkushner Gordon beat me to the comment - the same logic can be used with an `update` statement. See my edited answer. – Mureinik Dec 28 '15 at 19:05

Remove whitespace in mysql

1 Answers1