Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

SQL - Syntax help - string manipulation - SQL Server 2005

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
robthomas09
Asked:
robthomas09
3 Solutions
 
SharathData EngineerCommented:
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
 
Ephraim WangoyaCommented:
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
 
cyberkiwiCommented:
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
 
robthomas09Author Commented:
Thanks!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now