This is an open article without any completion in mind; to set the proper case of a name, as in capitalize the first letter, lower case the rest but then with exceptions.
Why?
Some of the Zoho Apps are incredibly case-sensitive and consider some records containing people's names as duplicates, when actually they are the same entity but may have uppercased a letter in their name while the other entry does not...
This article is a start and adaptation of my previous article Convert to Proper Case in T-SQL which I had previously used for the migration of a HR system. This is intended to work using Zoho Deluge.
How?
There are a number of combinations that I won't have thought of but the below code sufficed for my current task, common English and some European names:
// // set first and last name v_FirstName = "BILLIE-JO"; v_LastName = "MCDONALD-O'LEARY ii OF CAMBRIDGE-worcester-OXFORD"; // // let's deal with double or triple barrelled first names v_FirstName = v_FirstName.replaceAll("-", " :|JOEL|: ", true).proper(); v_FirstName = v_FirstName.replaceAll(" :|joel|: ", "-", true); // // Names starting with Mc, O', D', I' l_ExceptionPrefixes = {"Mc", "O'", "D'", "I'"}; // // deal with double barrelled surnames l_FormattedLastName = List(); l_LastNameParts = v_LastName.toList("-"); for each v_LastNamePart in l_LastNameParts { // // default to proper v_LastNamePart = v_LastNamePart.proper(); // // if exception prefixes for each v_Prefix in l_ExceptionPrefixes { if(v_LastNamePart.indexOf(v_Prefix)==0) { v_LastNamePart = v_LastNamePart.replaceFirst(v_Prefix,v_Prefix + " :|JOEL|: ", true).proper(); v_LastNamePart = v_LastNamePart.replaceAll(" :|joel|: ", "", true); } } // // add back to our hyphen separated list l_FormattedLastName.add(v_LastNamePart); } v_LastName = l_FormattedLastName.toString("-") + " "; // // affix exceptions l_Exceptions = {" II", " III", " the ", " de ", " of ", " van ", " and "}; for each v_Exception in l_Exceptions { v_LastName = v_LastName.replaceAllIgnoreCase(v_Exception, v_Exception, false); } v_LastName = v_LastName.trim(); // // join all together v_FullName = trim(v_FirstName + " " + v_LastName); info v_FullName; // // yields: Billie-Jo McDonald-O'Leary II of Cambridge-Worcester-Oxford
- //
- // set first and last name
- v_FirstName = "BILLIE-JO";
- v_LastName = "MCDONALD-O'LEARY ii OF CAMBRIDGE-worcester-OXFORD";
- //
- // let's deal with double or triple barrelled first names
- v_FirstName = v_FirstName.replaceAll("-", " :|JOEL|: ", true).proper();
- v_FirstName = v_FirstName.replaceAll(" :|joel|: ", "-", true);
- //
- // Names starting with Mc, O', D', I'
- l_ExceptionPrefixes = {"Mc", "O'", "D'", "I'"};
- //
- // deal with double barrelled surnames
- l_FormattedLastName = List();
- l_LastNameParts = v_LastName.toList("-");
- for each v_LastNamePart in l_LastNameParts
- {
- //
- // default to proper
- v_LastNamePart = v_LastNamePart.proper();
- //
- // if exception prefixes
- for each v_Prefix in l_ExceptionPrefixes
- {
- if(v_LastNamePart.indexOf(v_Prefix)==0)
- {
- v_LastNamePart = v_LastNamePart.replaceFirst(v_Prefix,v_Prefix + " :|JOEL|: ", true).proper();
- v_LastNamePart = v_LastNamePart.replaceAll(" :|joel|: ", "", true);
- }
- }
- //
- // add back to our hyphen separated list
- l_FormattedLastName.add(v_LastNamePart);
- }
- v_LastName = l_FormattedLastName.toString("-") + " ";
- //
- // affix exceptions
- l_Exceptions = {" II", " III", " the ", " de ", " of ", " van ", " and "};
- for each v_Exception in l_Exceptions
- {
- v_LastName = v_LastName.replaceAllIgnoreCase(v_Exception, v_Exception, false);
- }
- v_LastName = v_LastName.trim();
- //
- // join all together
- v_FullName = trim(v_FirstName + " " + v_LastName);
- info v_FullName;
- //
- // yields: Billie-Jo McDonald-O'Leary II of Cambridge-Worcester-Oxford
Caveat(s):
- This solution does not cater for the Mac prefix; because "Mackayla", "Macie", "Maci", "Macala", "Mack", "Macoy", "Macey", "Macedonia"...
Source(s):
- Wikipedia: Category: Compound surnames
- Diana, Goddess of the Hunt - Every-Name Index to Family Group Sheets: Mac / Mc
- Wikipedia: List of family name affixes