Solved

SQL - Syntax help - string manipulation - SQL Server 2005

Posted on 2011-03-21
4
259 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now