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)
 Function to Break Full name into First ,

Author  Topic 

neeraj1401
Starting Member

36 Posts

Posted - 2009-01-21 : 06:28:57
Please help me to break Fullname into First Name , Midile Name and last name.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-21 : 06:32:46
select parsename(replace(Fullname,' ','.'),3) as 'firstname',parsename(replace(Fullname,' ','.'),2) as 'middlename',parsename(replace(Fullname,' ','.'),1) as 'lastname'

Jai Krishna
Go to Top of Page

neeraj1401
Starting Member

36 Posts

Posted - 2009-01-21 : 07:27:49
Thanks Jai for Quick reply ................
Go to Top of Page

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-21 : 07:45:24
Welcome

Jai Krishna
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-01-21 : 19:23:19
If only real names were always so simple:

select
parsename(replace(Fullname,' ','.'),3) as 'firstname',
parsename(replace(Fullname,' ','.'),2) as 'middlename',
parsename(replace(Fullname,' ','.'),1) as 'lastname'
from
( -- test data
select Fullname = 'James Van Der Beek' union all
select 'Harry Connick, Jr.'
)a


Results:
firstname middlename lastname
--------- ----------- ------------
Van Der Beek
NULL NULL NULL

(2 row(s) affected)




CODO ERGO SUM
Go to Top of Page

neeraj1401
Starting Member

36 Posts

Posted - 2009-01-22 : 08:06:57
Hi Thanks for the help.

I'm getting null values for the record which having more then 3 blanks spaces spaces.
like
select parsename(replace('I B M INDIA PVT LTD',' ','.'),3) ,
parsename(replace('I B M INDIA PVT LTD' ,' ','.'),2) ,
parsename(replace('I B M INDIA PVT LTD' ,' ','.'),1)

please suggest.

thanks in advance


quote:
Originally posted by Michael Valentine Jones

If only real names were always so simple:

select
parsename(replace(Fullname,' ','.'),3) as 'firstname',
parsename(replace(Fullname,' ','.'),2) as 'middlename',
parsename(replace(Fullname,' ','.'),1) as 'lastname'
from
( -- test data
select Fullname = 'James Van Der Beek' union all
select 'Harry Connick, Jr.'
)a


Results:
firstname middlename lastname
--------- ----------- ------------
Van Der Beek
NULL NULL NULL

(2 row(s) affected)




CODO ERGO SUM

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 12:03:00
how do you distingusih between first,middle & last name? for example in sample data MVJ gave what will be values for three fields for name James Van Der Beek?
Go to Top of Page

neeraj1401
Starting Member

36 Posts

Posted - 2009-01-23 : 00:40:33
thanks for reply.
I'm using blank for breaking the name. suppose 'I B M INDIA PVT LTD' is in my name field , I just want it will come like
I as First_name
PVT as last_name and rest in midile_name
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-23 : 00:57:11
hi neeraj,
Try this once
DECLARE @val varchar(128)
SET @val = 'I B M INDIA PVT LTD'
select left( @val,charindex(' ',@val,1)) as firstname ,
substring(@val,charindex(' ',@val,1)+1,len(substring(@val,charindex(' ',@val,1),len(@val)))-charindex(' ',reverse(@val),1)) as middlename,
right(@val,charindex(' ',reverse(@val),1)) as lastname
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 01:21:30
quote:
Originally posted by neeraj1401

thanks for reply.
I'm using blank for breaking the name. suppose 'I B M INDIA PVT LTD' is in my name field , I just want it will come like
I as First_name
PVT as last_name and rest in midile_name




SELECT case when charindex(' ',Val)>0 then STUFF(Val,charindex(' ',Val),LEN(Val),'') else Val END AS First,
case when charindex(' ',Val)>0 then STUFF(STUFF(Val,1,charindex(' ',Val),''),len(STUFF(Val,1,charindex(' ',Val),''))+1-charindex(' ',reverse(STUFF(Val,1,charindex(' ',Val),''))),len(Val),'') else NULL END AS Middle,
case when charindex(' ',Val)>0 then STUFF(Val,1,len(Val)-charindex(' ',reverse(Val)),'') else NULL END AS Last
FROM
(
SELECT 'James Van Der Beek' AS Val UNION ALL
SELECT 'Ramu Chotta Dev Prasad' UNION ALL
SELECT 'Jose Andrews' UNION ALL
SELECT 'Karthik' UNION ALL
SELECT 'M M K Mani'
)t


output
--------------------------------------------
First Middle Last
--------------------------------
James Van Der Beek
Ramu Chotta Dev Prasad
Jose NULL Andrews
Karthik NULL NULL
M M K Mani
Go to Top of Page

neeraj1401
Starting Member

36 Posts

Posted - 2009-01-23 : 01:39:30
thanks visakh16 its working fine ....greate job :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 02:22:12
welcome
Go to Top of Page
   

- Advertisement -