Solved

SPLIT FULLNAME INTO LASTNAME,FIRSTNAME

Posted on 2013-01-09
3
418 Views
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
0
Comment
Question by:zachvaldez
3 Comments
 
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.
0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 100 total points
ID: 38759958
check http://www.sql-server-helper.com/tips/split-name.aspx

replace charindex(' '...

with
replace charindex(','...
0
 
LVL 6

Accepted Solution

by:
PJBX earned 100 total points
ID: 38759965
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

785 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