Flipping “surname, firstname” to “fullname” with just MYSQL

SQL

I love a good quick data fix and recently found this one on stackoverflow.

Say you have a MYSQL field with this format:

Surname, Forename

and you want to get into a new field:

Forename Surname

You can do this from the SQL prompt:

# Add new column
alter table $TABLE add $newField varchar(40); # or something like that

# Populate new data
update $TABLE set $newField = CONCAT(
  SUBSTRING_INDEX(SUBSTRING_INDEX($oldField,',',2),',',-1),
  ' ',
  SUBSTRING_INDEX($oldField,',',1)
);

Great stuff and very quick too!

Source – http://stackoverflow.com/questions/2654716/how-do-i-flip-a-column-in-mysql-that-has-data-structured-last-name-first-name

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s