MySQL : how to remove double or more spaces from a string?

user_402786 0
Viewed 49685

I couldn't find this question for MySQL so here it is:

I need to trim all double or more spaces in a string to 1 single space.

For example: "The   Quick  Brown    Fox" should be : "The Quick Brown Fox"

The function REPLACE(str, "  ", " ") only removes double spaces, but leaves multiples spaces when there are more...

mysql string
12 Answers

Here's an old trick that does not require regular expressions or complicated functions.

You can use the replace function 3 times to handle any number of spaces, like so:

REPLACE('This is    my   long    string',' ','<>')

becomes:

This<>is<><><><>my<><><>long<><><><>string

Then you replace all occurrences of '><' with an empty string '' by wrapping it in another replace:

REPLACE(
  REPLACE('This is    my   long    string',' ','<>'),
    '><',''
)

This<>is<>my<>long<>string

Then finally one last replace converts the '<>' back to a single space

REPLACE(
  REPLACE(
    REPLACE('This is    my   long    string',
      ' ','<>'),
    '><',''),
  '<>',' ')

This is my long string

This example was created in MYSQL (put a SELECT in front) but works in many languages.

Note that you only ever need the 3 replace functions to handle any number of characters to be replaced.

user_112999990

For MySQL 8+, you can use REGEXP_REPLACE function:

UPDATE `your_table` 
SET `col_to_change`= REGEXP_REPLACE(col_to_change, '[[:space:]]+', ' ');
user_88558730

Follow my generic function made for MySQL 5.6. My intention was to use regular expression to identify the spaces, CR and LF, however, it is not supported by this version of mysql. So, I had to loop through the string looking for the characters.

CREATE DEFINER=`db_xpto`@`%` FUNCTION `trim_spaces_and_crlf_entire_string`(`StringSuja` text) RETURNS text CHARSET utf8     COLLATE utf8_unicode_ci
DETERMINISTIC
BEGIN
DECLARE StringLimpa TEXT;
DECLARE CaracterAtual, CaracterAnterior TEXT;
DECLARE Contador, TamanhoStringSuja INT;

SET StringLimpa = '';
SET CaracterAtual = '';
SET CaracterAnterior = '';
SET TamanhoStringSuja = LENGTH(StringSuja);
SET Contador = 1;

WHILE Contador <= TamanhoStringSuja DO
    SET CaracterAtual = SUBSTRING(StringSuja, Contador, 1);

    IF ( CaracterAtual = ' ' AND CaracterAnterior = ' ' ) OR CaracterAtual = '\n' OR CaracterAtual = '\r' THEN
        /* DO NOTHING */
        SET Contador = Contador;
        /* TORNA OS ESPAÇOS DUPLICADOS, CR, LF VISUALIZÁVEIS NO RESULTADO (DEBUG)
        IF ( CaracterAtual = ' ' ) THEN SET StringLimpa = CONCAT(StringLimpa, '*');END IF;
        IF ( CaracterAtual = '\n' ) THEN SET StringLimpa = CONCAT(StringLimpa, '\\N');END IF;
        IF ( CaracterAtual = '\r' ) THEN SET StringLimpa = CONCAT(StringLimpa, '\\R');END IF;
        */
    ELSE
        /* COPIA CARACTER ATUAL PARA A STRING A FIM DE RECONSTRUÍ-LA SEM OS ESPAÇOS DUPLICADOS */
        SET StringLimpa = CONCAT(StringLimpa, CaracterAtual);
        /*SET StringLimpa = CONCAT(StringLimpa, Contador, CaracterAtual);*/
        SET CaracterAnterior = CaracterAtual;
    END IF;

    SET Contador = Contador + 1;
END WHILE;

RETURN StringLimpa;
END
user_31887760

In MySQL 8+:

SELECT REGEXP_REPLACE(str, '\\s+', ' ');
user_17674120
Related