Hi all, i have a column name as fullname. I want separate the fullname as firstname,midname,lastname. By using mid function how can we extract this.

[ashok1203]

Hi all, i have a column name as fullname. I want separate the fullname as firstname,midname,lastname. By using mid function how can we extract this.

ex: full name: Alexander De khoo  from this i want to separate

First name: Alexander

mid name: De

lastname: khoo

[jpenuliar]

two first Names like:

John Michael T. Vincent

[fvelascog72]

Here in Spain for example: Jose Manuel Perez Perez

[chrismarlow]

You might also put the 'van'/'von' in many Dutch/German names as the middle name ...

[jpenuliar]

you might end up splitting  "Dela Cruz" Family Name

[jpenuliar]

or people who do not use middle names like

"Charlie Chaplin"

[ankitaag]

Hi,

mid(FullName,1,index(FullName,' ',2)-1) as FirstName,

mid(FullName,index(FullName,' ',3)+1,index(FullName,' ',3)-(index(FullName,' ',2)+1)) as MiddleName,

mid(FullName,index(FullName,' ',3)+1) as LastName

This would give you Jose Manuel as FirstName in case of Jose Manuel Perez Perez

[chrismarlow]

It is a minefield .

[jpenuliar]

Jose Manuel Perez Perez Senior

[ankitaag]

Hi Jonathan, Federico and Christopher,

Kindly go through the below script for names having FirstName,MiddleName,LastName or Names having two first names or names not having middle name:

Temp:

LOAD * INLINE [

    FullName

    Jose Manuel Perez Perez

    John Michael T. Vincent

    Charlie Chaplin

    Alexander De Khoos

];

T1:

Load FullName,

if(SubStringCount(FullName,' ')=1,mid(FullName,1,index(FullName,' ',1)-1),

  if(SubStringCount(FullName,' ')=2,mid(FullName,1,index(FullName,' ',1)-1),

  mid(FullName,1,index(FullName,' ',2)-1))) as FirstName,

if(SubStringCount(FullName,' ')=1,'',

  if(SubStringCount(FullName,' ')=2,mid(FullName,index(FullName,' ',1)+1,index(FullName,' ',2)-(index(FullName,' ',1)+1)),

  mid(FullName,index(FullName,' ',3)+1,index(FullName,' ',3)-(index(FullName,' ',2)+1)))) as MiddleName,

if(SubStringCount(FullName,' ')=1,mid(FullName,index(FullName,' ',1)+1),

  if(SubStringCount(FullName,' ')=2,mid(FullName,index(FullName,' ',2)+1),

  mid(FullName,index(FullName,' ',3)+1))) as LastName

Resident Temp;

drop table Temp;

Thanks and Regards,

Ankita

[fvelascog72]

It work fine for these examples BUT it can´t work for all posibilities like people with complex names and surnames.

For example: Maria del Pilar Perez de la Rosa