How to remove multiple whitespaces, new lines, carriage return, and tab in a string in MySQL?

Question

How do you perform the following PHP function in MySQL using UPDATE, SET and REPLACE?

preg_replace('/\s+/', ' ', $string);

from Remove multiple whitespaces

EDIT QUESTION

My objectif is to go from the follwing string

[!DOCTYPE html]
[!--[if IE 8]][html class="no-js lt-ie9" lang="es" ][![endif]--]
[!--[if gt IE 8]][!--] [html class="no-js" lang="es" ] [!--[![endif]--]
[html lang="es-ES" prefix="og: http://ogp.me/ns#"]
[head]

to the above

[!DOCTYPE html][!--[if IE 8]][html class="no-js lt-ie9" lang="es" ][![endif]--]
[!--[if gt IE 8]][!--][html class="no-js" lang="es" ][!--[![endif]--][html lang="es-ES" prefix="og: http://ogp.me/ns#"][head]

clearing all the \r \n \t

\n = new line \r = carriage return \t = tab

EDIT ANSWER

The full answer using UPDATE, SET and REPLACE in addition to Elias Van Ootegem's suggestion

UPDATE tab
SET col = REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(col, '~', ''), 
                '\t', '' -- replace tabs
            ),
            '\n','' -- replace *NIX line-feeds
        ),
        '\r', -- replace DOS line-feeds
        ''
    )

Have you even tried... there's thousands of dupes for this question: http://stackoverflow.com/questions/6858143/mysql-how-to-remove-white-space-in-a-mysql-field — Elias Van Ootegem, Sep 30 '13 at 13:12

score 3 · Accepted Answer · answered Sep 30 '13 at 13:50

To replace all whitespace chars from any string in mysql, you'll have to call REPLACE a couple of times:

SELECT
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(fieldName, ' ', ''), -- replace spaces
                '\t', '' -- replace tabs
            ),
            '\n','' -- replace *NIX line-feeds
        ),
        '\r', -- replace DOS line-feeds
        ''
    );

That should cover them all. If I forgot about one, just add another REPLACE( /*replace calls here*/, '~', ''), to add that to the list.
MySQL isn't that good at processing text, though, so whatever you'll attempt, it'll feel a tad clunky

score 0 · Answer 2 · answered Sep 30 '13 at 13:12

0

You can try trim and replace function

UPDATE `table` SET `col_name` = TRIM( `col_name` )

UPDATE `table` SET `col_name` = REPLACE( `col_name` , ' ' , '' )

I hope it may solve the problems

answered Sep 30 '13 at 13:12

geekdev

1,192
11
15

Thanks, I have tried but TRIM or REPLACE doesn't clear all the whitespaces (\r\n\t) in between the string – RafaSashi Sep 30 '13 at 13:39
sorry can you edit your answer so I can vote you up? – RafaSashi Sep 30 '13 at 14:21

How to remove multiple whitespaces, new lines, carriage return, and tab in a string in MySQL?

2 Answers2