How to reverse two strings in a single column?

Posted on 2007-08-07
Last Modified: 2013-11-05
We are running MySQL as the backend for a document management system.  Unfortunately, things were well underway before I arrived on the scene.  Now, we have have about 44,000 records in the DB that need to be fixed.  The problem is that all names were entered FIRST LAST instead of LAST, FIRST.

I've read the other posts that talk of how to select the last name, but I don't want it merely selected or even split into a second column.  I need the last name, re-inserted into the field and formated LAST, FIRST.

Also, I want to work on a backup table instead of the actual data.  Once I succeed in reversing the two names, can I simply rename the new table to replace the old one? I want to ensure there's no unique identifier that I risk screwing up that way.

Your help would be greatly appreciated.  
Question by:fuze44
    LVL 4

    Expert Comment

    Well, assuming that you do not have any keys/constraints defined on the name field and that you know how to "select" the first name and last name, the easier way to do it would be:

    1. add a new NAME column in the table,
    2. update the column with new values
    3. create new table by selecting the newly added column and leaving out the old name field.
    4. drop/rename the original table
    5. rename the new table

    If you need help in selecting the data from old column and checking the constraints on the same, it would help if you can specify the table structure here.

    Hope that helps..

    LVL 19

    Accepted Solution

    are there middle names? split names, hyphenated names? duplicate names?

    if i am reading this correctly, then you have this part

    DECLARE @FullName        VARCHAR(100)
    DECLARE @FirstName       VARCHAR(100)
    DECLARE @LastName        VARCHAR(100)
    SET @FullName = 'John Doe'
    SET @FirstName = (SELECT (LEFT(@FullName, NULLIF(CHARINDEX(' ', @FullName) - 1, -1))))
    SET @LastName  = (SELECT (RIGHT(@FullName, ISNULL(NULLIF(CHARINDEX(' ', REVERSE(@FullName)) - 1, -1), LEN(@FullName)))))
    select (@LastName + ', ' + @FirstName) as name


    Author Comment

    Thanks for the replies.  Honestly, I'm in over my head on this.  I'm not familiar enough with MySQL to do this with much level of confidence.  I've acquired some books to do some prep work so I can back up my tables before trying anything.  For now, I need to ask:

    nitinmehta: Is it necessary to create a new table instead of adding a field, moving data, and then deleting old field?

    weellio: Can those commands be entered from the Query Browser?
    LVL 19

    Expert Comment

    the only way to know is to try it out  :)
    short answer = yes. it will diplay the following

    Doe, John

    what i provided was a simple way to split the first name and the last name from a given variable, then patch them together as lastname, firstname.

    the example 'john doe' gets turned into doe, john.

    basically you input this into a function and pipe it through your database to replace all the names.
    but honestly i think it would be better to have first and last name in sepereate fields. and if you want a combined field, then have that on top of the uncombined ones.
    LVL 4

    Expert Comment

    It is possible to drop the field but in some cases, it might break your code. For ex:

    insert into tab_name values(123, something, 'something');

    here, you are not explicitly specifying the column names so when you add the column and remove the old one, serial of column names will change and it may break such code, if there is any.

    Although, there are options to specify the position of new column added into table as well, but because you say, you have absolutely no experience on mysql, i wouldn't ask you to do that.
    LVL 4

    Assisted Solution

    If you are still interested in doing it this way, here is the syntax:

    alter table <table_name> ADD <COLUMN_name> <column_definition>  AFTER <your name column>;

    This will add the column after your name column, so when you drop the old name column, with:

    alter table <table_name> DROP <your name column>;

    you'll have the new column at the place of old column. Now rename the new column to old column with:

    alter table <table_name> CHANGE <your name column> <NEW_COLUMN_NAME> <column_definition>;

    Hope this helps...

    Best Regards

    Author Comment

    Sorry about the long delay.  This was put onto the back burner and never resumed, but it is still an unresolved issue.  I'd like to keep it open for a while longer to get to try the solutions (I wear all of the hats around here).

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now