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

Question

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...

Check this answer http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql . The OP wants to do the same but with other characters. — Augusto, Aug 04 '11 at 11:33
Why don't you run the REPLACE(str," ",""); multiple times? :) — Tomasz, Apr 28 '17 at 21:48

xytyx · Answer 1 · 2020-09-01T09:54:13.317

38

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.

edited Sep 01 '20 at 09:54

answered Apr 02 '19 at 13:38

xytyx

381
3
3

Smart answer, but it's quite unreadable compared to stored functions. – shaahiin Dec 11 '19 at 11:22
You rock! Nice solution. – Jiří Chmiel Feb 25 '20 at 10:08
I like this solution: it is simple yet creative. Personally I like to execute the replace function in this example in three separate queries instead of a replace in a replace in a replace. – Arno Visser Aug 29 '20 at 06:13
1

This obviously does not work if your string contains one of those characters. `my > string` becomes `my >string` – Lehren Jun 22 '21 at 12:21
Ahah I like this little magic trick :) – Arlaf Mar 15 '22 at 17:29
Beautiful hack. I had to expand this for my case by first replacing tabs with spaces - `REPLACE(reference, '\t', ' ')` – Adam Moore Aug 10 '22 at 16:00

score 25 · Answer 2 · answered Mar 03 '16 at 17:08

25

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:]]+', ' ');

answered Mar 03 '16 at 17:08

tomconnors

885
9
11

Not working for me: REGEXP_REPLACE(LCASE(`metapackages`->>'$.data[0].title'), '[[:space:]]+', ' ') – Umair Hamid Jul 18 '19 at 13:54
1

If you're fortunate to use mysql 8 this works as well – Jacques Koorts Mar 03 '21 at 12:44
Smart and simple answer, great! – papesky Dec 02 '21 at 17:07
Unlike solutions that involve `REPLACE()`, this one takes care of other whitespace chars as well - spaces, tabs, newlines and carriage returns. – binaryLV Jan 28 '22 at 15:33
Works great! Also in combination with TRIM. Like this for example: `REGEXP_REPLACE(TRIM(column), "[[:space:]]+", " ")` – rf1234 Aug 27 '22 at 13:04
While unfortunately specific to MariaDB, this is the type of solution that the asker was seeking. – Justin Jan 15 '23 at 19:58

score 24 · Answer 3 · answered Apr 12 '13 at 12:41

24

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

answered Apr 12 '13 at 12:41

alkoln

599
5
14

score 17 · Answer 4 · answered Aug 13 '12 at 19:40

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

score 11 · Answer 5 · edited Sep 13 '16 at 18:20

11

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

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

edited Sep 13 '16 at 18:20

LeonanCarvalho

1,819
3
20
39

answered Aug 04 '11 at 12:05

nobody

10,599
4
26
43

yes it does `SELECT REPLACE( REPLACE( "a[5 spaces]b", " ", " " ), " ", " " )'` result: `"a b"` – nobody Aug 04 '11 at 12:09
it don't, try `SELECT length(REPLACE( REPLACE( "a b", " ", " " ), " ", " " ));`, it return 4 instead of 3 – ajreal Aug 04 '11 at 12:11
@ajreal well, seems you're right, the more nesting you do the more spaces it can handle, but there must be a way to make this work, I'll keep thinking – nobody Aug 04 '11 at 12:15
haha ... well done, but it fails if the spaces more than 6, you just need a WHILE loop when spaces is more than 2, however, mysql not truly support WHILE loop, as suggested previously, define your procedure function to do this – ajreal Aug 04 '11 at 12:33
looks like there is no way to make this work, edited and changed the tone of my answer :) – nobody Aug 04 '11 at 12:33
`REPLACE(REPLACE(REPLACE(REPLACE(str,'(5)',' '),'(4)',' '),'(3)',' '),'(2)',' ')` clears up to 38 spaces rather than the 8 spaces given in this answer. – ChiMo May 27 '16 at 04:31
Enough for the most of purposes. – dlopezgonzalez Dec 21 '16 at 09:55

score 5 · Answer 6 · answered Sep 21 '11 at 13:20

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 :)

such a nice function... congratz – TroniPM Jan 23 '20 at 01:33 — TroniPM, Jan 23 '20 at 01:33

score 2 · Answer 7 · edited Jul 13 '21 at 03:41

2

For MySQL 8+, you can use REGEXP_REPLACE function:

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

edited Jul 13 '21 at 03:41

Madhur Bhaiya

28,155
10
49
57

answered Oct 17 '18 at 12:58

Imranul Islam

21
2

Perfect solution for MySQL 8+ – Madhur Bhaiya Jul 13 '21 at 03:37

score 1 · Answer 8 · answered Jan 10 '22 at 20:18

1

In MySQL 8+:

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

answered Jan 10 '22 at 20:18

But those new buttons though..

21,377
10
81
108

score 0 · Answer 9 · answered Jan 28 '16 at 16:20

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

score 0 · Answer 10 · 2017-04-04T01:41:57.037

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>";
}

score 0 · Answer 11 · answered Oct 14 '18 at 11:41

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

score 0 · Answer 12 · answered Aug 04 '11 at 19:43

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

score 0 · Answer 13 · answered Feb 14 '23 at 00:32

0

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"

answered Feb 14 '23 at 00:32

SHdez96

59
2

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

13 Answers13

Linked