Posted on 2013-01-09
Last Modified: 2013-01-10
I have a 'Fullname' column in a table in the format
Lastname, Firstname.
Id like to create two cols taht would split the Last and First name.

What is the update statement to do that? thanks
Question by:zachvaldez
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 40 total points
ID: 38759956
First I'll make an assumption that you're adding two new columns to your table, called Surname and First_Name

With that assumption in place:

update employees
set Surname = substring(Fullname, 1, charindex(',', Fullname)-1),
    First_name = ltrim(rtrim(substring(Fullname, charindex(',', Fullname)+1, len(Fullname)))) 

Open in new window

This will work as long as the name is always as specified, but should work whether there's a space after the comma or not.
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 100 total points
ID: 38759958

replace charindex(' '...

replace charindex(','...

Accepted Solution

PJBX earned 100 total points
ID: 38759965
1. First create the FirstName and LastName columns in your table

2. Run the following select:
    LEFT(FullName, CHARINDEX(',', FullName, 1) - 1) AS FirstName,  
    RTRIM(LTRIM(STUFF(FullName, 1, CHARINDEX(',', FullName, 1), ''))) AS LastName  

3. After confirming the Select. Run the update:
Update YourTableName
SET            FirstName = RTRIM(LTRIM(STUFF(FullName, 1, CHARINDEX(',', FullName, 1), ''))),
            LastName = LEFT(FullName, CHARINDEX(',', FullName, 1) - 1)

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
shrink table after huge delete 2 28
EditableGrid how to fetch rows from MySql in php 14 48
SQL Syntax 6 43
T-SQL: I need to add an index on a field 5 28
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 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