using string function ..index,left,mid etc

[Anonymous]

I have a column with name like

John_Doe

Mary Jane

Charles,Ray

I want to break them into first and lastname

[Gysbert_Wassenaar]

Perhaps like this:

If(index(Name,',',subfield(Name, ',',-1),

     if(index(Name,'_',subfield(Name,'_',1),

          if(index(Name,' ',subfield(Name,' ',1))) as FirstName

If(index(Name,',',subfield(Name, ',',1),

     if(index(Name,'_',subfield(Name,'_',-1),

          if(index(Name,' ',subfield(Name,' ',-1))) as LastName

[Anonymous]

Thank you for the quick reply

But it gives me error,may be I missed something

I'm attaching a sample file here

[sunny_talwar]

Try this may be:

Test:

LOAD If(KeepChar(Name, '_, ') = ',', SubField(Name, ',', 2),

  If(KeepChar(Name, '_, ') = '_', SubField(Name, '_', 1),

  If(KeepChar(Name, '_, ') = ' ', SubField(Name, ' ', 1)))) as [First Name],

  If(KeepChar(Name, '_, ') = ',', SubField(Name, ',', 1),

  If(KeepChar(Name, '_, ') = '_', SubField(Name, '_', 2),

  If(KeepChar(Name, '_, ') = ' ', SubField(Name, ' ', 2)))) as [Last Name];

Load *

INLINE [

    Name

    John_Doe

    Mary Jane

    "Charles,Ray"

];

[sunny_talwar · Accepted solution]

Or even this would work:

Test:

LOAD Pick(Match(KeepChar(Name, '_, '), ',', '_', ' '), SubField(Name, ',', 2), SubField(Name, '_', 1), SubField(Name, ' ', 1)) as [First Name],

  Pick(Match(KeepChar(Name, '_, '), ',', '_', ' '), SubField(Name, ',', 1), SubField(Name, '_', 2), SubField(Name, ' ', 2)) as [Last Name];

Load *

INLINE [

    Name

    John_Doe

    Mary Jane

    "Charles,Ray"

];

[Anonymous]

Thank you Sunny !

[Kushal_Chawda]

another way to do is like below

Test:

Load *, subfield(replace(replace(Replace(Name,'_','*'),' ','*'),',','*'),'*',1) as [First Name],

subfield(replace(replace(Replace(Name,'_','*'),' ','*'),',','*'),'*',2) as [Last Name]

INLINE [

    Name

    John_Doe

    Mary Jane

    "Charles,Ray"

];

[Anonymous]

Sunny,

Do you maintain a blog ?

If no you should write one.

Regards

B

[sunny_talwar]

Hahaha thanks Balraj‌‌ for seeing the potential in me .

I don't have a blog, but its on radar.

Best,

Sunny