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

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 f开发者_JAVA技巧unction REPLACE(str, "  ", " ") only removes double spaces, but leaves multiples spaces when there are more...

---

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.

---

The shortest and, surprisingly, the fastest solution:

CREATE FUNCTION clean_spaces(str VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
    while instr(str, '  ') > 0 do
        set str := replace(str, '  ', ' ');
    end while;
    return trim(str);
END

---

I know this question is tagged with mysql, but if you're fortunate enough to use MariaDB you can do this more easily:

SELECT REGEXP_REPLACE(column, '[[:space:]]+', ' ');

---

DELIMITER //

DROP FUNCTION IF EXISTS DELETE_DOUBLE_SPACES//
CREATE FUNCTION DELETE_DOUBLE_SPACES ( title VARCHAR(250) )
RETURNS VARCHAR(250) DETERMINISTIC
BEGIN
    DECLARE result VARCHAR(250);
    SET result = REPLACE( title, '  ', ' ' );
    WHILE (result <> title) DO 
        SET title = result;
        SET result = REPLACE( title, '  ', ' ' );
    END WHILE;
    RETURN result;
END//
DELIMITER ;

SELECT DELETE_DOUBLE_SPACES('a    b');

---

This solution isn't very elegant but since you don't have any other option:

UPDATE t1 set str = REPLACE( REPLACE( REPLACE( str, "  ", " " ), "  ", " " ), "  ", " " );

---

After searching I end up writing a function i.e

drop function if exists trim_spaces;

delimiter $$

CREATE DEFINER=`root`@`localhost` FUNCTION `trim_spaces`(`dirty_string` text, `trimChar` varchar(1))
    RETURNS text
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
  declare cnt,len int(11) ;
  declare clean_string text;
  declare chr,lst varchar(1);

  set len=length(dirty_string);
  set cnt=1;  
  set clean_string='';

 while cnt <= len do
      set  chr=right(left(dirty_string,cnt),1);           

      if  chr <> trimChar OR (chr=trimChar AND lst <> trimChar ) then  
          set  clean_string =concat(clean_string,chr);
      set  lst=chr;     
     end if;

     set cnt=cnt+1;  
  end while;

  return clean_string;
END
$$
delimiter ;

USAGE:

set @str='------apple--------banana-------------orange---' ;

select trim_spaces( @str,'-')

output: apple-banana-orange-

parameter trimChar to function could by any character that is repeating and you want to remove .

Note it will keep first character in repeating set

cheers :)

---

For MySQL 8+, you can use REGEXP_REPLACE function:

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

---

This is slightly general solution: from

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56195&whichpage=1

create table t (s sysname)
insert into t select 'The   Quick  Brown    Fox'
-- convert tabs to spaces
update  t set s = replace(s, '  ',' ')
where   charindex(' ', s) > 0

-- now do the work.
while 1=1
begin
    update t
    set     s = substring(s, 1, charindex('  ', s, 1)-1) + ' ' + ltrim(substring(s,charindex('  ', s, 1), 8000))
    where   charindex('  ', s, 1) > 0

    if @@rowcount = 0
        break
end

select  s
from    t

---

If the string that you want to convert consists of only alphabets and multiple number of spaces [A-Za-z ]* then the following function will work. I found out a pattern when such strings are converted to hex. Based on that my solution follows. Not so elegant, but it doesn't require any procedures.

unhex(
replace(
replace(
replace(
replace(
replace(
replace(
hex(str)
,204,1014)
,205,1015)
,206,1016)
,207,1017)
,20,'')
,101,20)
)

---

If you are using php....

try{
$con = new PDO ("mysql:host=localhost;dbname=dbasename","root","");
}
catch(PDOException $e){
echo "error".$e-getMessage();   
}

$select = $con->prepare("SELECT * FROM table");
$select->setFetchMode(PDO::FETCH_ASSOC);
$select->execute();

while($data=$select->fetch()){ 

$id = $data['id'];
$column = $data['column'];

$column = trim(preg_replace('/\s+/',' ', $column)); // remove all extra space


$update = $con->prepare("UPDATE table SET column=:column WHERE id='$id'");
$update->bindParam(':column', $column );
$update->execute();

// echo $column."<br>";
} 

---

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

---

In MySQL 8+:

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

---

you can try removing more tan one space with regex

SELECT REGEXP_REPLACE('This is my long string',' +', ' ');

the result would be this: "This is my long string"