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

[mphekin12]

If you are always going to have a First, Mid and Last name you could use the following:

SubField(FullName, ' ', 1) as FirstName,

SubField(FullName, ' ', 2) as MidName,

SubField(FullName, ' ', 3) as LastName,

[buzzy996]

as mphekin12 said,u have to use the subfield function & based space position u can split name into 3 parts.

[prabhas277]

HI,

=mid('Alexander De khoo',1,10) returns  the "Alexander" u can treat as first name

=mid('Alexander De khoo',11,2) returns the De treat as second name

=mid('Alexander De khoo',14,4) returns the khoo treat as last name..

Hope it will work

regards,

suresh

[buzzy996]

hi, I hope name is dynamic here..so the above example won't work for all the possibilities .

he supposed to use subfield()!

[ashok1203]

Hi,mphekin12 Thanks for reply, And your is correct But i want Using mid function how i can extract those.

[ashok1203]

Hi prabhas277 , I want to apply whole column at a time by using mid function.

[ankitaag]

Hello Ashok,

Below is the script you can try to separate your FullName to FirstName, MiddleName and LastName:

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

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

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

from Table

Explanation:

FirstName = mid(FullName,1,index(FullName,' ',1)-1) (1st parameter is the string, 2nd parameter is the starting index, 3rd parameter first calculates the index position of the 1st space and subtracting 1 resulting the last character before 1st space.)

MiddleName = mid(FullName,index(FullName,' ',1)+1,index(FullName,' ',2)-(index(FullName,' ',1)+1)) (1st parameter is the string, 2nd parameter first calculates the index position of the 1st space and adding 1 to get the index position of the character after the 1st space, 3rd parameter subtractes the index position of the 1st space and 2nd space)

LastName = mid(FullName,index(FullName,' ',2)+1) (1st parameter is the string, 2nd parameter first calculates the index position of the 2nd space and adding 1 to get the index position of the character after the 1st space, 3rd parameter is not specified which means till the end)

Thanks and Regards,

Ankita

[jpenuliar]

and how about in the case of two first Names?

[ankitaag]

Hi Jonathan,

Two first names as in?

Can you please be specific with an example?

Thanks and Regards,

Ankita