replace multiple space with single space in a column

Question

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?

https://stackoverflow.com/questions/6940646/mysql-how-to-remove-double-or-more-spaces-from-a-string — Fanosss test, Oct 25 '18 at 15:01

score 8 · Answer 1 · answered May 04 '20 at 13:49

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:

first replace all the single space with open/closed characters like <> , or [] or {}...
Then replace the back to back reversed order characters (closed/open) with empty value, so all >< or ][ or }{ will be removed.
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, ' ', '<>'), '><', ''),'<>',' ');

For dialects without regex, this is a fantastic solution. +1 — Bilbottom, Jan 13 '21 at 16:19
When I first glossed over this answer, I thought you were some crazy kook. Then I actually read it. Turns out, this is ingenious! This should be the accepted answer. I have been looking for a solution like this for a decade. This solves all the `CONCAT_WS(" ",` problems when there's no regex. — , May 23 '21 at 11:41

Madhur Bhaiya · Accepted Answer · 2018-10-25T15:14:45.450

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)

Tried and got the same problem - nothing is replaced. The query is transformed the same way as mine. — , Oct 25 '18 at 15:02
@puerto please run it from Terminal/Command line interface etc. PHPMyAdmin maybe doing some own thng. Run it from some application code (eg: PHP) — Madhur Bhaiya, Oct 25 '18 at 15:04

score 4 · Answer 3 · edited Jan 15 '23 at 17:46

4

You can try the following SELECT example, with REGEXP_REPLACE is used. For example:

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`;

REGEXP_REPLACE documentation

You can use REGEXP_REPLACE in an UPDATE statement:

UPDATE abc SET title = REGEXP_REPLACE(title, ' \+', ' ');

edited Jan 15 '23 at 17:46

magdmartin

1,712
3
20
43

answered Oct 25 '18 at 15:04

Prasad Wargad

737
2
7
11

score 0 · Answer 4 · answered May 03 '22 at 17:48

0

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.

answered May 03 '22 at 17:48

Niresh Gopalakrishnan

25
1
5

replace multiple space with single space in a column

4 Answers4