trimming extra spaces from large DB file

[Ragnorack67@hotmail.com]

Hi, I need some advice on what to do. I have this MySQL insert file,
with about 30,000 records. One of the datafields has names in it. When
this list was put together, apparently there was extra space formatting
inserted at the end of each name. I want to remove these, so for
example:


INSERT INTO `table_one` VALUES ('John Doe ', 1);
INSERT INTO `table_one` VALUES ('Jane Doe ', 2);
INSERT INTO `table_one` VALUES ('Baby S. Doe ', 3);

I want:

INSERT INTO `table_one` VALUES ('John Doe', 1);
INSERT INTO `table_one` VALUES ('Jane Doe', 2);
INSERT INTO `table_one` VALUES ('Baby S. Doe', 3);

I was thinking some kind of script could be put together to trim the
extra spaces out. I have this entire insert list as a text file and can
re-insert it, or leave it in the DB and run queries against it. What
complicates this for me is some of the name fields have middle names,
some do not. Maybe something to remove all spaces greater than one on a
first pass, and then on a second pass trim the extra space at the end
only. That is what I think of in theory - but have no idea how to
create such script. Can anyone help? I would be very much appreciated.

Sincerely,

Alex

[Peter Albertsson]

Re: trimming extra spaces from large DB file

MySQL has a trim function so I think the easiest way would be to load the
file into MySQL and then:

UPDATE table_one SET `name` = TRIM(`name`);

Replace `name` with whatever the column name is.

Best Regards,

Peter Albertsson


<Ragnorack67@ho tmail.com> wrote in message
news:1111512496 .786536.102590@ o13g2000cwo.goo glegroups.com.. .[color=blue]
> Hi, I need some advice on what to do. I have this MySQL insert file,
> with about 30,000 records. One of the datafields has names in it. When
> this list was put together, apparently there was extra space formatting
> inserted at the end of each name. I want to remove these, so for
> example:
>
>
> INSERT INTO `table_one` VALUES ('John Doe ', 1);
> INSERT INTO `table_one` VALUES ('Jane Doe ', 2);
> INSERT INTO `table_one` VALUES ('Baby S. Doe ', 3);
>
> I want:
>
> INSERT INTO `table_one` VALUES ('John Doe', 1);
> INSERT INTO `table_one` VALUES ('Jane Doe', 2);
> INSERT INTO `table_one` VALUES ('Baby S. Doe', 3);
>
> I was thinking some kind of script could be put together to trim the
> extra spaces out. I have this entire insert list as a text file and can
> re-insert it, or leave it in the DB and run queries against it. What
> complicates this for me is some of the name fields have middle names,
> some do not. Maybe something to remove all spaces greater than one on a
> first pass, and then on a second pass trim the extra space at the end
> only. That is what I think of in theory - but have no idea how to
> create such script. Can anyone help? I would be very much appreciated.
>
> Sincerely,
>
> Alex
>[/color]