Split Full Name into three fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Split Full Name into three fields

Author

Topic

Sirrombor
Starting Member

12 Posts

Posted - 2003-11-17 : 17:05:13

Hello,

I am still learning TSQL =)

I need to split my Full Name field into First, Last fields.

I really only need First and Last names divided. Titles, Middle names and extras can be ignored.

Here is an example of my current full names field:
John Doe
Jane Doe-Cole
John Joe Doe
John O'Brian Sr.
Dr. John Doe

It seems like a common task, so can someone point me to a tutorial or script?

Thanks in advance

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-17 : 22:41:12

[code]
create table #name ( n int identity(0,1),name varchar(20))

insert into #name
select 'Dr John Doe' union select 'Mrs Jane Doe-Cole' union select 'John Joe Doe'
union select 'John O''Brian Sr.' union select 'Dr. John Doe Dodo'

create table #titles (n int identity(0,1),title varchar(10))

insert into #titles
select 'Dr' union select 'Mrs' union select 'Sr' union select '.'

declare @n int,@title varchar(10)
set @n = 0

while @n <= (select max(n) from #titles)
begin
select @title = title from #titles where n = @n
update #name set name = ltrim(rtrim(replace(name,@title,''))) from #name
set @n = @n+1
end

update #name set name = replace(name,' ','.')

select
ISNULL(parsename(name,3),parsename(name,2))firstname,
CASE WHEN parsename(name,3) IS NULL then NULL ELSE parsename(name,2) END middlename,
parsename(name,1) lastname
from #name

drop table #titles
drop table #name
[/code]

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-18 : 19:03:15

Great, Jay!

Such superb thoroughly elaborated solution..

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-18 : 20:50:50

Thanx Stoad

Just learned that PARSENAME function..

Sirrombor
Starting Member

12 Posts

Posted - 2003-12-01 : 13:50:43

Thanks ehorn....

Sorry for the late reply, I just now got back to this and your solution worked great!!

I had trouble figuring out what PARSENAME was doing until I read this which made it all click.....
The PARSENAME() function can be useful for parsing small strings. It returns parts 1-4 (working right to left) of a string, with each part delimited by periods.

For example:

PARSENAME('most.valuable.yak',3) returns “most”

PARSENAME('most.valuable.yak',1) returns “yak”

PARSENAME('most.valuable.yak',4) returns NULL

Thanks again for your help..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-01 : 13:53:27

Damian wrote a very good article on PARSENAME:

http://www.sqlteam.com/item.asp?ItemID=15044

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources