Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL - Syntax help - string manipulation - SQL Server 2005

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
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…

809 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