Split Names

[Thuky]

Hi Everyone,

I need a quick help. I have to split last name from a column that stores the whole name (first name and last name). The following names like:

Lee, Terry
Mary A. Sanchez
Stacey Smith
Lynn Mary & Henson

I really appreciate for any help.

 

[amorous]

Your names are not consistent and i guess you might end up writing a complex function...

but if you have all your names in the following format

Firstname, Lastname

then

Something like this will work...

nameVar= Lee, Terry

newnameVar=Split(nameVar,",")

then

newnameVar(0)=Lee
newnamevar(1)=Terry


if you have names in this format

Firstname Lastname

Stacey Smith


nameVar= Stacey Smith

newnameVar=Split(nameVar,"")

then

newnameVar()= Stacey
newnamevar(1)=Smith



hope this gives you some idea

-VJ

 

[ESquared]

Have you Searched The Fine Web? I remember seeing a few threads here on tek-tips about this very thing, and I have read several articles online complete with full code to do this.

 

[Glasgow]

If it is safe to assume that the last name always follows the last space then something like

SELECT LastName=RIGHT(FullName,CHARINDEX(' ',REVERSE(FullName)))

might do the trick

 

[Glasgow]

Apologies - that won't work for the Lee,Terry scenario (assuming Lee is the surname) but can extend to

SELECT LastName=
  CASE WHEN CHARINDEX(',',FullName)<>0
       THEN LEFT(FullName,CHARINDEX(',',FullName)-1)
       ELSE RIGHT(FullName,CHARINDEX(' ',REVERSE(FullName)))
       END

 

[Thuky]

Hi Glasgow,
According to the code below:
CODE
SELECT LastName=
CASE WHEN CHARINDEX(',',FullName)<>0
THEN LEFT(FullName,CHARINDEX(',',FullName)-1)
ELSE RIGHT(FullName,CHARINDEX(' ',REVERSE(FullName)))
END

It works perfect for Lee, Terry.

However, the Else case (ELSE RIGHT(FullName,CHARINDEX(' ',REVERSE(FullName)))) doesn't work as expected for:

Mary A. Sanchez
Stacey Smith

Stacey Smith is like this htimS yecatS.

Any help, please.

 

[ESquared]

just put another reverse() around that else clause