Solved

SQL - Syntax help - string manipulation - SQL Server 2005

Posted on 2011-03-21
4
264 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
[X]
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
4 Comments
 
LVL 41

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:Ephraim Wangoya
Ephraim Wangoya 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

688 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