How to update 200000 records in mySQL with single space in name field

Question

I have more than 200000 records in my database with the customers name and information. The customer name has much space between first name and last name.

ex.  1.ADAMS         CRAIG
     2. GABRIEL       GEANETTE
     3. KRANTZ        AUDREY

How I can remove the space in between. like

ex.  1.ADAMS CRAIG
     2.GABRIEL GEANETTE
     3.KRANTZ AUDREY

Any helps will be appreciate.

Thank You,

AFAIK: the only way to do this purely in MySQL would be to create a function that replaces multiple spaces with single spaces, and it gets ugly. Otherwise, doing a regular expression replacement in PHP would probably be easier. — jeffjenx, Dec 19 '12 at 19:33

score 4 · Accepted Answer · edited May 23 '17 at 10:24

4

Taken from the following answer... and modified slightly to peform an update:

UPDATE YourTable
SET Name = 
        replace(
          replace(
             replace(
                LTrim(RTrim(Name)),         --Trim the field
             '  ',' |'),                    --Mark double spaces
          '| ',''),                         --Delete double spaces offset by 1
       '|','')                              --Tidy up
WHERE Name LIKE '%  %'

So in your example...

ADAMS         CRAIG

Would become...

ADAMS | | | | CRAIG

And once the special | token is removed, it becomes...

ADAMS CRAIG

And one final removal of | will handle even vs odd numbers of spaces.

edited May 23 '17 at 10:24

Community

1
1

answered Dec 19 '12 at 19:39

Michael Fredrickson

36,839
5
92
109

1

+1 very nice, and does it all in one pass. But this assumes you know that pipes aren't included in any of the strings... which will generally be the case – PinnyM Dec 19 '12 at 19:43
1

@PinnyM Yes, good point... whatever special token is used shouldn't be present in the string... you can even use a non-printable character such as `char(1)` as the token. – Michael Fredrickson Dec 19 '12 at 19:44
Does my more than 200000 records at one time... Thanks lot @MichaelFredrickson – krish kim Dec 19 '12 at 19:47
nice idea, i was not sure it would be possible in just one pass :) +1 – fthiella Dec 19 '12 at 19:49
In a more general case, it would be nice if there was no requirement for the non-occurrence of a "special" token character within the source. It would be nice to have a solution that operated only on contiguous space characters. – spencer7593 Dec 19 '12 at 22:30

Salman A · Answer 2 · 2012-12-19T19:42:28.057

1

Run this query a few times until affected rows become 0.

UPDATE tbl
SET    col = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(col, '  ', ' '), '  ', ' '), '  ', ' '), '  ', ' '), '  ', ' '))
WHERE  col LIKE '%  %'

Convert this query to a select query and test. If results are satisfactory, go ahead with the update.

edited Dec 19 '12 at 19:42

answered Dec 19 '12 at 19:36

Salman A

262,204
82
430
521

score 1 · Answer 3 · answered Dec 19 '12 at 19:45

mysql> select name from tmp;
+------------------------+
| name                   |
+------------------------+
| ADAMS         CRAIG    |
| GABRIEL       GEANETTE |
| KRANTZ        AUDREY   |
+------------------------+
3 rows in set (0.00 sec)

mysql> select concat(substr(name,1,locate(' ',name)), trim(substr(name,locate(' ', name)))) from tmp;
+-------------------------------------------------------------------------------+
| concat(substr(name,1,locate(' ',name)), trim(substr(name,locate(' ', name)))) |
+-------------------------------------------------------------------------------+
| ADAMS CRAIG                                                                   |
| GABRIEL GEANETTE                                                              |
| KRANTZ AUDREY                                                                 |
+-------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

spencer7593 · Answer 4 · 2012-12-19T22:50:52.460

As an alternative to the selected answer, this statement will remove spans of contiguous space characters from a column and replace the spaces with a single space character. This operates ONLY on the space characters, and does not require any special "token" character (and does not require that the the special "token" character NOT appear within the source column.

UPDATE mytable
   SET mycol =
       REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(
         mycol
       ,SPACE(21),' ')
       ,SPACE(13),' ')
       ,SPACE(8),' ')
       ,SPACE(5),' ')
       ,SPACE(3),' ')
       ,SPACE(2),' ')
 WHERE mycol LIKE '%  %'

To run this expression as a test, use it in a SELECT statement:

SELECT REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(
       REPLACE(
         mycol
       ,SPACE(21),' ')
       ,SPACE(13),' ')
       ,SPACE(8),' ')
       ,SPACE(5),' ')
       ,SPACE(3),' ')
       ,SPACE(2),' ') AS new_mycol
    FROM mytable t
   WHERE t.mycol LIKE '%  %'

This could be extended with larger spans of SPACE characters, for fewer replacements. The replacements start with the largest spans of contiguous space characters, and then the replacements are progressively smaller, to catch all of the occurrences of spaces as efficiently as possible. (This isn't the most "efficient" algorithm, but it works suitably.)

How to update 200000 records in mySQL with single space in name field

4 Answers4