query to separate first last and middle name

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

General SQL Server Forums

New to SQL Server Programming

query to separate first last and middle name

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-07-22 : 07:57:41

Hi all,
I have a col as name which includes full name as below

Name
-----------
Maloe,Suzan M
Conn,Christopher Z
Van Pess,Wen P
aak, cse

while inserting in to other table from the above name, I need to split it into LastName,FirstName,MilddleName ( Maloe=LastName Suzan=FirstName M=Middlenname).

Can any one help me in writing a select query to split this.

Regards,
aak

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-07-22 : 08:03:13

select
name
,LTRIM(RTRIM(Substring(Name,1,Patindex('%,%',Name)-1))) lastName
,LTRIM(RTRIM(Substring(Name,Patindex('%,%',Name)+1,len(name)))) fristName
,NULL
from table

the above works up to some extent however getting middle name and cleaning firstname has to be done..

Regards,
aak

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-22 : 08:20:53

If you try "first last and middle name" in google custom search in the right upper corner of this forum then you will get some solutions.

No, you're never too old to Yak'n'Roll if you're too young to die.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-22 : 09:28:31

Hi aakcse

Try this...

CREATE TABLE #temp
             (
                          IND   INT IDENTITY(1,1),
                          ENAME VARCHAR(100)
             )--Length its upto Req...
INSERT
INTO   #temp
SELECT 'Maloe, Suzan M'

UNION ALL

SELECT 'Conn, Christopher Z'

UNION ALL

SELECT 'Van, PessWen P'

UNION ALL

SELECT 'Asif, Khan'


SELECT LEFT(ENAME,CHARINDEX(',',ENAME)-1)                          ,
       SUBSTRING(ENAME,CHARINDEX(',',ENAME)+1,CHARINDEX(' ',ENAME)),
       CASE
              WHEN CHARINDEX(' ',REPLACE(ENAME,', ','##'))>0
              THEN LTRIM(SUBSTRING(ENAME,CHARINDEX(' ',REPLACE(ENAME,', ','##')),2))
              ELSE ''
       END
FROM   #TEMP

-------------------------
R..

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-07-22 : 09:33:22

[code]
DECLARE @t TABLE
(Names VARCHAR(100))
INSERT INTO @T SELECT
'Maloe,Suzan M' UNION ALL SELECT
'Conn,Christopher Z' UNION ALL SELECT
'Van Pess,Wen P' UNION ALL SELECT
'Van Pess,TEST' UNION ALL SELECT
'Asif,Khan'

SELECT LEFT(Names,CHARINDEX(',',NAMES)-1) as FirstName
,CASE WHEN CHARINDEX(' ',REVERSE(names))=0 OR
CHARINDEX(' ',REVERSE(names))> CHARINDEX(',',REVERSE(names))
THEN NULL
ELSE LEFT(REVERSE(NAMES),CHARINDEX(' ',REVERSE(NAMES))-1) END AS MiddleName

,CASE CHARINDEX(' ',SUBSTRING(Names, CHARINDEX(',',Names)+1,LEN(names)))
WHEN 0
THEN SUBSTRING(Names, CHARINDEX(',',Names)+1,LEN(names))
ELSE LEFT(SUBSTRING(Names, CHARINDEX(',',Names)+1,LEN(names)), CHARINDEX(' ',SUBSTRING(Names, CHARINDEX(',',Names)+1,LEN(names))))
END LastName

FROM @T

[/code]

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-07-22 : 11:01:44

Mangal Thank you so much, this logic is too good..
Thanks rajdaksha and Others all.

CASE CHARINDEX(' ',SUBSTRING(Name, CHARINDEX(',',Name)+1,LEN(name)))
WHEN 0
THEN SUBSTRING(Name, CHARINDEX(',',Name)+1,LEN(name))
ELSE LEFT(SUBSTRING(Name, CHARINDEX(',',Name)+1,LEN(name)), CHARINDEX(' ',SUBSTRING(Name, CHARINDEX(',',Name)+1,LEN(name))))
END FirstName
,LTRIM(RTRIM(LEFT(Name,CHARINDEX(',',NAME)-1))) as LastName
,CASE WHEN CHARINDEX(' ',REVERSE(name))=0 OR
CHARINDEX(' ',REVERSE(name))> CHARINDEX(',',REVERSE(name))
THEN NULL
ELSE LEFT(REVERSE(NAME),CHARINDEX(' ',REVERSE(NAME))-1)
END AS MiddleName

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-07-22 : 11:47:54

This function has been tried and tested over a decade:
http://sqlblindman.pastebin.com/f68f37c15

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

blackair
Starting Member

1 Post

Posted - 2010-10-07 : 21:43:01

LEFT(Name,CHARINDEX(' ',NAME)-1) as FirstName
,CASE
WHEN CHARINDEX(' ',REVERSE(Name))+ CHARINDEX(' ',NAME) < LEN(Name)
THEN SubString(Name, CHARINDEX(' ', (Name))+1, (LEN(Name)-CHARINDEX(' ', REVERSE(Name))-CHARINDEX(' ',Name)))
Else 'Null'
END AS MiddleName
,RIGHT(Name, CHARINDEX(' ', REVERSE(NAME))-1 ) AS LastName

figmo
Starting Member

18 Posts

Posted - 2011-04-27 : 13:29:28

Thanks for the suggestions. What I ended up doing is porting the code from my custom function to inline in the select statement and that made HUGE improvements in my query time. But damn.....is it ever UGLY! Below is the part of my select statement that does the name manipulation:


CASE WHEN CHARINDEX(', ', Artist) < 1 THEN
	Artist
ELSE
	CASE WHEN CHARINDEX('&', SUBSTRING(Artist,CHARINDEX(', ', Artist),LEN(ARTIST))) < 1 THEN 
		SUBSTRING(Artist, CHARINDEX(', ', Artist)+2, LEN(ARTIST))
	ELSE
		LEFT(SUBSTRING(Artist, CHARINDEX(', ', Artist)+2, LEN(ARTIST)), CHARINDEX('&',SUBSTRING(Artist,CHARINDEX(', ', Artist)+2,LEN(ARTIST)))-1)					 
	END
END + ' ' +

CASE WHEN CHARINDEX(', ', Artist) < 1 THEN
	''
ELSE			
	SUBSTRING(Artist, 0, CHARINDEX(', ', Artist))	
END  + ' ' + 

CASE WHEN CHARINDEX(', ', Artist) < 1 THEN
	''
ELSE
	CASE WHEN CHARINDEX('&', Artist) < 1 THEN
	    ''
	ELSE
		SUBSTRING(Artist, CHARINDEX('&', Artist), LEN(ARTIST))
	END
END

Question: Is there anyway to create a temp variable in the select statement? In my function (or stored procedures) I can DECLARE a var, and then SET it. This is useful for remembering parts of the algorithm that are used more than once (e.g. an int holding the position of the comma character). Without this, I have to repeat these CHARINDEX calls each time I need that value.

My performance with the above is pretty good - but always looking to tweak it even faster.

nikita2010
Starting Member

1 Post

Posted - 2012-07-13 : 11:55:14

Hello All,

I have a requirement where the name field had both first and last name. Some seperated with spaces and some with comma. If they are seperated with comma, anything before , goes to lastname and after that is firstname. If it is seperated by space, anything before first space goes to firstname and after the first space is lastname.
Any help is much appreciated.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-14 : 00:01:49

quote:
Originally posted by nikita2010

Hello All,

I have a requirement where the name field had both first and last name. Some seperated with spaces and some with comma. If they are seperated with comma, anything before , goes to lastname and after that is firstname. If it is seperated by space, anything before first space goes to firstname and after the first space is lastname.
Any help is much appreciated.

Thanks.


SELECT CASE WHEN CHARINDEX(' ',Col)>0 THEN PARSENAME(REPLACE(Col,' ','.'),2) WHEN CHARINDEX(',',Col) > 0 THEN PARSENAME(REPLACE(Col,',','.'),1) END AS FirstName,
CASE WHEN CHARINDEX(' ',Col)>0 THEN PARSENAME(REPLACE(Col,' ','.'),1) WHEN CHARINDEX(',',Col) > 0 THEN PARSENAME(REPLACE(Col,',','.'),2) END AS LastName
FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources