'replace multiple space with single space in a column

There are a lot of rows with multiple spaces in column title and I want to replace them with a single space.

update abc set title = REPLACE(title, "  ", " ");

Nothing is replaced.

I'm using phpMyAdmin.

I noticed (clicking on the button Simulate query that my query is transformed into: 

update abc set title = REPLACE(title, " ", " ");

so replace single space with single space.

Any help?

mysql


Solution 1:[1]

Number of consecutive space characters can either be odd or even. You can replace two space characters with one space character, and do a similar replace again on the modified string to cover all the odd/even cases.

UPDATE abc SET title = REPLACE(REPLACE(title, '  ', ' '), '  ', ' ');

Explanation:

  • 2 spaces: First replace will convert to 1 space. Second replace will not modify further.
  • 3 spaces: First replace will convert (2+1) spaces to (1+1). Second will convert (1+1 = 2) spaces to 1 space.
  • 4 spaces: First replace will convert (2+2) spaces to (1+1). Second will convert (1+1 = 2) spaces to 1 space.

and so on...

DEMO:

mysql> select 
    -> dt.test_str, 
    -> REPLACE(REPLACE(dt.test_str, '  ', ' '), '  ', ' ') AS modified
    -> FROM 
    -> (SELECT 'thi   s is  a weird    string' AS test_str) AS dt ;
+--------------------------------+--------------------------+
| test_str                       | modified                 |
+--------------------------------+--------------------------+
| thi   s is  a weird    string | thi s is a weird string |
+--------------------------------+--------------------------+
1 row in set (0.00 sec)

Solution 2:[2]

While checking this page it came to my attention that to replacing all the double spaces from the database you might have triple space or more on the single record.

The thing that the some solution didn't take in consideration.So you need to make sure that your statement replace them all. Doing one time or two time replacement of double space with single space might not cover all the corrupted data.

For example having a record value as 'A B C'; what you can do is:

  1. first replace all the single space with open/closed characters like <> , or [] or {}...
  2. Then replace the back to back reversed order characters (closed/open) with empty value, so all >< or ][ or }{ will be removed.
  3. Final step is to restore the single spaces by replacing the remaining open/close characters with single space, for example <> will be changed back to ' '

I always use something like following to fix my data:

UPDATE Table1 SET Column1 = REPLACE(REPLACE(REPLACE(Column1, ' ', '<>'), '><', ''),'<>',' ');

Solution 3:[3]

You can try this SELECT example, where REGEXP_REPLACE is used. Just too have assurity it's working:

SELECT 'ab  asd   asd a qeqw q    qwe   qweqw qw' AS `text 1`, REGEXP_REPLACE('ab  asd   asd a qeqw q    qwe   qweqw qw', ' \+', ' ') AS `text 2`;

Used REGEXP_REPLACE
https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-replace

You can use REGEXP_REPLACE in update too:

update abc set title = REGEXP_REPLACE(title, ' \+', ' ');

Solution 4:[4]

Here is my approach

SELECT ARRAY_TO_STRING(ARRAY_AGG(VALUE::varchar),' ') FROM TABLE(flatten(split(REGEXP_REPLACE('','\n'),' '))) WHERE VALUE <> '' ORDER BY INDEX;

might be a long route but does the job.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Omar Mughrabi
Solution 3
Solution 4 Niresh Gopalakrishnan

Related Questions