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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

717 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