Checking the format of a string

[Ciara]

I have a field that holds customers names. 

Eg, Lastname,Firstname

I want to create a flag that will check for the above format. 

For example: Does it meet the following criteria

• there are 2 substrings delimited by a comma
• the first substring starts with an uppercase letter followed by lowercase
• the second substring starts with an uppercase letter followed by lowercase

If the field meets all the criteria above, then give it a flag of 1, otherwise, give it a flag of 0.

Thanks

Ciara

[Taoufiq_Zarra]

Ciara  like :

if(substringcount(Field,',')>0 and len(keepchar(left(Field,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))>0 and len(keepchar(mid(Field,index(Field,',')+1,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))>0,'1','0') as Flag 

and example :

load *,
if(substringcount(Field,',')>0 and len(keepchar(left(Field,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))>0 and len(keepchar(mid(Field,index(Field,',')+1,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))>0,'1','0') as Flag inline [
Field
"A,B"
"AB"
"Ajhjh,Bjhjhj"
"ajhjh,Bjhgjhsd"
"Ajjdj,bjdjdj"
"Azert,Querty"

];

output:

[Kushal_Chawda]

@Ciara  Assuming that you always have two strings which could be separated by any delimiter. Also assuming that there is no other special characters in string

With above assumption you can try below

LOAD *,
     if(Index(String,','),
     if(SubField(String,',',1)=Capitalize(SubField(String,',',1)) and
     SubField(String,',',2)=Capitalize(SubField(String,',',2)),1,0),0) as Flag;
LOAD * Inline [
String
"Abc,Xyz"
"abc,xyx"
"Pqr,xyx"
abc xyz
Abc Xyx];

[Ciara]

@Kushal_Chawda 

This works beautifully.  Thank you.

However, I forgot I live in Ireland (how awful of me 😊) and we have names like OConnell, or OReilly, or McMahon etc.

So while the flag is certainly working, its flagging the instances of the above example as not meeting the criteria (which of course it doesn't) so how could I get around this?

Maybe I could add a wildmatch and just ignore names that begin with O or Mc perhaps?  Or add a wildmatch and have a different criteria for these?

[Kushal_Chawda]

@Ciara  I would suggest if you can share few of those names with full name then I can try update the code instead of ignoring

[Ciara]

Absolutely @Kushal_Chawda  thanks a mil.

McDermott,Sean James

McCall,Ashley

OKeeffe,Rosanna

ODwyer,Orla

OGara,Ruth

McCarthy,Eilis

[Kushal_Chawda]

@Ciara  Just one question, if you have name like Mcdermott,Sean James. Do you still want to flag 1? Because after "Mc" letter is small "d"

[Taoufiq_Zarra]

@Ciara  you can also try below

load Field,if(substringcount(Field,',')>0 and len(keepchar(mid(Field,IDtmp,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))>0 and len(keepchar(mid(Field,index(Field,',')+1,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))>0,'1','0') as Flag;
load *,
if(Match(left(Field,1),'O'),2,if(Match(left(Field,2),'Mc'),3,1)) as IDtmp 
Inline [
Field
"McDermott,Sean James"
"Mcdermott,Sean James2"
"McCall,Ashley"
"OKeeffe,Rosanna"
"Okeeffe2,Rosanna"
"ODwyer,Orla"
"OGara,Ruth"
"McCarthy,Eilis"
"A,B"
"AB"
"Ajhjh,Bjhjhj"
"ajhjh,Bjhgjhsd"
"Ajjdj,bjdjdj"
"Azert,Querty"
];

output:

[Kushal_Chawda]

@Ciara 

LOAD *,
     if(Index(String_temp,','),
     if(SubField(String_temp,',',1)=Capitalize(SubField(String_temp,',',1)) and 
     SubField(String_temp,',',2)=Capitalize(SubField(String_temp,',',2)),1,0),0) as Flag;
LOAD *,
     if(match(left(String,1),'O'),mid(String,2,Len(String)),
     if(match(left(String,2),'Mc'),mid(String,3,Len(String)),String)) as String_temp;
LOAD * Inline [
String
"Abc,Xyz"
"abc,xyx"
"Pqr,xyx"
abc xyz
Abc Xyx
"McDermott,Sean James"
"Mcdermott,Sean James"
"McCall,Ashley"
"OKeeffe,Rosanna"
"ODwyer,Orl"
"OGara,Ruth"
"McCarthy,Eilis"];

[Kushal_Chawda]

@Ciara  If you want more precise solution where if actual name is started from "O" like example "Oxford"  the above all solution won't work as expected so try below

LOAD *,
     if(Index(String_temp,','),
     if(SubField(String_temp,',',1)=Capitalize(SubField(String_temp,',',1)) and 
     SubField(String_temp,',',2)=Capitalize(SubField(String_temp,',',2)),1,0),0) as Flag;
LOAD *,
     if(match(left(String,1),'O') and mid(String,2,1)=Capitalize(mid(String,2,1)),mid(String,2,Len(String)),
     if(match(left(String,2),'Mc'),mid(String,3,Len(String)),String)) as String_temp;
LOAD * Inline [
String
"Abc,Xyz"
"abc,xyx"
"Pqr,xyx"
abc xyz
Abc Xyx
"McDermott,Sean James"
"Mcdermott,Sean James"
"McCall,Ashley"
"OKeeffe,Rosanna"
"ODwyer,Orl",
"Oxford,Orl"
"OGara,Ruth"
"McCarthy,Eilis"];