Subfield

[renuka_sasikumar]

Name

Parvinder Kaur

John P Gonsalve

Ritesh Gupta

Anita G Rodrigues

Deepali R Jahav

Rupesh J Sonawane

Fatima C Fernandes

Sunil S Singh

Rohit J Gupta

How do I remove the middle name and display the First Name n Last Name.

[miha_pucelj]

One option would be

subfield(Name, ' ' ,1) & ' ' & subfield(Name, ' ' ,3) As Name

[#3]

try this

subfield(Name, ' ' ,1) & ' ' & subfield(Name, ' ' ,3) As Name_new

[hic]

Hard coding the number 3 in the subfield() function is not a good idea, since you then assume that there always is a middle name. Better then to use

     subfield(Name, ' ' ,1) & ' ' & subfield(Name, ' ' ,-1) As Name

where -1 means the last subfield.

HIC

[renuka_sasikumar]

I tried this but i m getting Parvinder instead of Parvinder Kaur.

The name which does not have middle name its first name only cuming

Can u help me to get also Parvinder Kaur & Ritesh Gupta

[MK_QSL]

Something like below...

IF(Len(SubField(Name, ' ',2))>1, SubField(Name,' ',1)&' '&SubField(Name, ' ',2),

  SubField(Name,' ',1)&' '&SubField(Name, ' ',3)) as NAME

[Jason_Michaelides]

I'm not sure the above solutions will work where there is no middle name.  Maybe try something like:

SubField(Name,' ',1) & ' ' & SubField(Name,' ',-1)

Hope this helps,

Jason

[#8]

Because you can have 2 or 3 (or more) words, I would also use substringcount() function

subfield(name, ' ', 1) & '-' & subfield(name, ' ', substringcount(name, ' ')+1) as newName

Fabrice

[renuka_sasikumar]

Thank u very much it worked.

[Jason_Michaelides]

There's something weird going on with the timing of posts appearing...! When I gave my answer, only the ones by Miha and Vish were present - even after posting and reloading.  Then suddenly all the others appeared - included one from HIC the same as mine!  Ah well - better to have too many answers than none at all!!