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.
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 |
|
|
neeraj1401
Starting Member
36 Posts |
Posted - 2009-01-21 : 07:27:49
|
Thanks Jai for Quick reply ................ |
|
|
Jai Krishna
Constraint Violating Yak Guru
333 Posts |
Posted - 2009-01-21 : 07:45:24
|
WelcomeJai Krishna |
|
|
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 BeekNULL NULL NULL(2 row(s) affected) CODO ERGO SUM |
|
|
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 BeekNULL NULL NULL(2 row(s) affected) CODO ERGO SUM
|
|
|
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? |
|
|
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 |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-01-23 : 00:57:11
|
hi neeraj,Try this onceDECLARE @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 |
|
|
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 LastFROM(SELECT 'James Van Der Beek' AS Val UNION ALLSELECT 'Ramu Chotta Dev Prasad' UNION ALLSELECT 'Jose Andrews' UNION ALLSELECT 'Karthik' UNION ALLSELECT 'M M K Mani' )toutput--------------------------------------------First Middle Last--------------------------------James Van Der BeekRamu Chotta Dev PrasadJose NULL AndrewsKarthik NULL NULLM M K Mani |
|
|
neeraj1401
Starting Member
36 Posts |
Posted - 2009-01-23 : 01:39:30
|
thanks visakh16 its working fine ....greate job :) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-23 : 02:22:12
|
welcome |
|
|
|
|
|
|
|