CODEWITHSUNDEEP
mysql
user7461846
user7461846

Reputation:

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?

Upvotes: 7

Views: 16694

Answers (4)

Prasad Wargad
Prasad Wargad

Reputation: 765

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

Upvotes: 6

Niresh Gopalakrishnan
Niresh Gopalakrishnan

Reputation: 25

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.

Upvotes: 0

Omar Mughrabi
Omar Mughrabi

Reputation: 133

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

Upvotes: 9

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

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)

Upvotes: 5

Related Questions