Solved

SQL - Syntax help - string manipulation - SQL Server 2005

Posted on 2011-03-21
4
261 Views
Last Modified: 2012-05-11
Hello experts.

I have a table that houses demographic information.  It sadly holds the name of a person in one full column, and I want to break it apart into columns for last_name, first_name, and middle_name.  Some of them have middle names and some do not.

I would like to go from:

table: person
name                       first_name           last_name          middle_name
Jones, Mike A          null                      null                      null
Smith, Jim B             null                      null                      null
Reynolds, Sarah     null                      null                      null

to:

table: person
name                       last_name           first_name          middle_name
Jones, Mike A         Jones                 Mike                    A
Smith, Jim B            Smith                  Jim                      B
Reynolds, Sarah     Reynolds           Sarah                 null

Maybe something like:

update person
set last_name = replace(name,RIGHT(name,charindex(',',reverse(name))-1),''),
first_name = RIGHT(name,charindex(',',reverse(name))-1),
middle_name = RIGHT(name,charindex(' ',reverse(name))-1)

Thoughts?

Thanks!


0
Comment
Question by:robthomas09
4 Comments
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 150 total points
ID: 35184553
Do you always have comma as seperator between last and first names?
Are there any multiple words seperated with space in last/first names?
Do you have any dots in the name?
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 150 total points
ID: 35184624
try
update person
set last_name  = LEFT(name, charindex(',', name) -1),
    first_name = REVERSE(SUBSTRING(REVERSE(RTRIM(SUBSTRING(@full, charindex(',', name)+1, LEN(name)))), 2, LEN(name))),
    middle_name= LEFT(LTRIM(REVERSE(name)), 1)

Open in new window

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 200 total points
ID: 35184842
sample table with all sorts of inconsistent data

create table person(
name varchar(100), first_name varchar(100),
last_name varchar(100), middle_name varchar(100))
insert person (name) select 'Jones, Mike A'
insert person (name) select 'Smith, Jim B'
insert person (name) select 'Reynolds, Sarah'
insert person (name) select ''
insert person (name) select 'john doe'
insert person (name) select 'john, blah junior Dr.'

Open in new window


Function to split the name
create function dbo.splitName(@name nvarchar(max))
returns @res table(last nvarchar(max), first nvarchar(max), mid nvarchar(max))
as
begin
declare @last nvarchar(max), @first nvarchar(max), @mid nvarchar(max)
set @last = left(@name+',', charindex(',', @name+',') -1)
set @last = left(@last+' .', charindex(' ', @last+' .') -1)
set @name = ltrim(rtrim(stuff(@name, 1, len(@last)+1, '')))
set @first = left(@name+',', charindex(',', @name+',') -1)
set @first = left(@first+' .', charindex(' ', @first+' .') -1)
set @mid = stuff(@name, 1, len(@first)+1, '')
insert @res select ltrim(rtrim(@last)), ltrim(rtrim(@first)), ltrim(rtrim(@mid))
return
end
GO

Open in new window


Sample usage

update person
set last_name = n.last,
    first_name = n.first,
    middle_name = n.mid
from person cross apply dbo.splitName(name) n

Open in new window


Result of select * from person

Jones, Mike A         Jones      Mike       A
Smith, Jim B          Smith      Jim        B
Reynolds, Sarah       Reynolds   Sarah      
                                 NULL       NULL
john doe              john       doe        
john, blah junior Dr. john       blah       junior Dr.

Open in new window

0
 

Author Closing Comment

by:robthomas09
ID: 35186422
Thanks!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

777 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