RE: Remove unwanted characters from a name field - Jerry Schwartz - com.mysql.lists.mysql

4 messages in com.mysql.lists.mysqlRE: Remove unwanted characters from a...

From	Sent On	Attachments
Mark	25 Oct 2006 10:17
Jerry Schwartz	25 Oct 2006 10:54
Chris W	25 Oct 2006 11:40
Daniel da Veiga	25 Oct 2006 13:21

Subject:	RE: Remove unwanted characters from a name field
From:	Jerry Schwartz (jsch...@the-infoshop.com)
Date:	10/25/2006 10:54:22 AM
List:	com.mysql.lists.mysql

I don't have any suggestions for a script, but I've wrestled with this issue before in other places. It is very difficult to regularize the format of a full name:

Johnson, Jr. - Fred Applewhite-Smith, Pamela Quimby III, Roger Alan

etc. I suppose it all depends upon how bad the existing data is, and how picky you are about the results.

You can probably do what you want with a combination of REGEXP, IF, SUBSTRING, and CONCAT; but it would take more time than I have to work it out.

Regards,

860.674.8796 / FAX: 860.674.8341

-----Original Message----- From: Mark [mailto:mswe...@gmail.com] Sent: Wednesday, October 25, 2006 1:18 PM To: mys...@lists.mysql.com Subject: Remove unwanted characters from a name field

I have a field called name which stores a person's first, middle and last name.

Previously, these names (17800 of them) were entered in the database by different people so there was no consistency in the format used. There are now names entered such as:

Smith, John S. Doe - Jane W. Doe John

What I would like to do is to have a script that can update the database and make all names appear with a comma separating the last and first name as shown in the first example: Smith, John S.

The rest of the name format does not matter.

Is this possible and if so, can someone be kind enough to help with such a script and or point me in the right direction.

Thanks