Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How to reverse two strings in a single column?

Posted on 2007-08-07
Medium Priority
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
  • 3
  • 2
  • 2

Expert Comment

ID: 19651773
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

William Elliott earned 1000 total points
ID: 19651913
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

ID: 19666182
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?
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

LVL 19

Expert Comment

by:William Elliott
ID: 19666740
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.

Expert Comment

ID: 19667953
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.

Assisted Solution

nitinmehta earned 1000 total points
ID: 19667981
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

ID: 22409283
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

580 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