SPLIT FULLNAME INTO LASTNAME,FIRSTNAME

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
zachvaldezAsked:
Who is Participating?
 
PJBXConnect With a Mentor Commented:
1. First create the FirstName and LastName columns in your table

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

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)
0
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
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.
0
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
check http://www.sql-server-helper.com/tips/split-name.aspx

replace charindex(' '...

with
replace charindex(','...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.