• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

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
0
zachvaldez
Asked:
zachvaldez
3 Solutions
 
Steve WalesSenior 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 MoreauSenior .Net ConsultantCommented:
check http://www.sql-server-helper.com/tips/split-name.aspx

replace charindex(' '...

with
replace charindex(','...
0
 
PJBXCommented:
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now