Using C

You can create Global Functions to use in your mappings by adding the functions to your "C# Global" code. To edit Global code, click the globe in the upper right corner of the mapping screen and select "C# Global".

At the top, be sure to include your "using" statements and create any global variables you want to use. After that, create your functions and classes. The following Global Code will help you to parse names and convert state names to state abbreviations.

To use the GetFullNameDto and GetStateByName methods, use code like the following:

string firstname = FullName.GetFullNameDto("Mr. & Mrs. Star & Clown Fish, MD").FirstName;
stateAbbreviation= GetStateByName("Illinois");

using System;
using System.Data;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Globalization;

public class FullNameDTO
{
    public string Prefix     { get; set; }
    public string FirstName  { get; set; }
    public string MiddleName { get; set; }
    public string LastName   { get; set; }
    public string Suffix     { get; set; }
}

public static class FullName
{
    public static FullNameDTO GetFullNameDto(string fullName)
    {
        TextInfo textInfo = new CultureInfo("en-US",false).TextInfo;
        string[] knownPrefixes    = { "mr", "mrs", "ms", "miss", "dr", "sir", "madam", "master", "fr", "rev", "atty", "hon", "prof", "pres", "vp", "gov", "ofc" };
        string[] knownSuffixes    = { "jr", "sr", "ii", "iii", "iv", "v", "esq", "cpa", "dc", "dds", "vm", "jd", "md", "phd", "frics" };
        string[] lastNamePrefixes = { "da", "de", "del", "dos", "el", "la", "st", "van", "von" };

        var prefix     = string.Empty;
        var firstName  = string.Empty;
        var middleName = string.Empty;
        var lastName   = string.Empty;
        var suffix     = string.Empty;

        var fullNameDto = new FullNameDTO();
        fullNameDto.Prefix     = prefix;
        fullNameDto.FirstName  = firstName;
        fullNameDto.MiddleName = middleName;
        fullNameDto.LastName   = lastName;
        fullNameDto.Suffix     = suffix;

        // Split on period, commas or spaces, but don't remove from results.
        //var namePartsList = Regex.Split(fullName, "(?<=[., ])").ToList();
        var stringArray = Regex.Split(fullName, "(?<=[., ])");
        var namePartsList = new List<string>(stringArray);

        #region Clean out the crap.
        for (var x = namePartsList.Count - 1; x >= 0; x--)
        {
            if (namePartsList[x].Trim() == string.Empty)
            {
                namePartsList.RemoveAt(x);
            }
        }
        #endregion

        #region Trim all of the parts in the list
        for (var x = namePartsList.Count - 1; x >= 0; x--)
        {
            namePartsList[x] = namePartsList[x].Trim();
        }
        #endregion

        #region Only one Name Part - assume a name like "Cher"
        if (namePartsList.Count == 1)
        {
            firstName = namePartsList[0].Replace(",", string.Empty).Trim();
            fullNameDto.FirstName = firstName;

            namePartsList.RemoveAt(0);
        }
        #endregion

        #region Get the Prefix
        if (namePartsList.Count > 0)
        {
            //If we find a prefix, save it and drop it from the overall parts
            var cleanedPart = namePartsList[0]
                                           .Replace(".", string.Empty)
                                           .Replace(",", string.Empty)
                                           .Trim()
                                           .ToLower();

            //if (knownPrefixes.Contains(cleanedPart))
            if (Array.IndexOf(knownPrefixes, cleanedPart) >= 0)
            {
                prefix = namePartsList[0].Trim();
                fullNameDto.Prefix = prefix;

                namePartsList.RemoveAt(0);
            }

            //Check if multiple prefixes exist
            bool multiplePrefixes = false;
            foreach (string n in namePartsList)
            {
                cleanedPart = n.Replace(".", string.Empty)
                                .Replace(",", string.Empty)
                                .Trim()
                                .ToLower();
                //if (knownPrefixes.Contains(cleanedPart))
                if (Array.IndexOf(knownPrefixes, cleanedPart) >= 0)
                {
                    multiplePrefixes = true;
                }
            }
            if (multiplePrefixes && (namePartsList[0] == "&" || namePartsList[0] == "and"))
            {

                prefix             = namePartsList[0];
                fullNameDto.Prefix = fullNameDto.Prefix + " " + prefix;
                namePartsList.Remove(namePartsList[0]);
                
                prefix             = namePartsList[0];
                fullNameDto.Prefix = fullNameDto.Prefix + " " + prefix;
                namePartsList.Remove(namePartsList[0]);
            }
        }
        #endregion

        #region Get the Suffix
        if (namePartsList.Count > 0)
        {
            #region Scan the full parts list for a potential Suffix
            foreach (var namePart in namePartsList)
            {
                var cleanedPart = namePart.Replace(",", string.Empty)
                                          .Trim()
                                          .ToLower();

                //if (!knownSuffixes.Contains(cleanedPart.Replace(".", string.Empty))) { continue; }
                if (!(Array.IndexOf(knownSuffixes, cleanedPart.Replace(".", string.Empty)) >= 0)) { continue; }

                if (namePart.ToLower() == "jr" && namePart != namePartsList[namePartsList.Count-1]) { continue; }

                suffix             = namePart.Replace(",", string.Empty).Trim();
                fullNameDto.Suffix = suffix;

                namePartsList.Remove(namePart);
                break;
            }
            #endregion
        }
        #endregion

        //If, strangely, there's nothing else in the overall parts... we're done here.
        if (namePartsList.Count == 0) { 
            fullNameDto.Prefix     = textInfo.ToTitleCase(fullNameDto.Prefix);
            fullNameDto.FirstName  = textInfo.ToTitleCase(fullNameDto.FirstName);
            fullNameDto.MiddleName = textInfo.ToTitleCase(fullNameDto.MiddleName);
            fullNameDto.LastName   = textInfo.ToTitleCase(fullNameDto.LastName);
            fullNameDto.Suffix     = textInfo.ToTitleCase(fullNameDto.Suffix);
            return fullNameDto; 
        }

        #region Prefix/Suffix taken care of - only one "part" left.
        if (namePartsList.Count == 1)
        {
            //If no prefix, assume first name (e.g. "Cher"), otherwise last (e.g. "Dr Jones", "Ms Jones")
            if (prefix == string.Empty)
            {
                firstName = namePartsList[0].Replace(",", string.Empty).Trim();
                fullNameDto.FirstName = firstName;
            }
            else
            {
                lastName = namePartsList[0].Replace(",", string.Empty).Trim();
                fullNameDto.LastName = lastName;
            }
        }
        #endregion

        #region fullName contains an & or an and.
        //else if ((namePartsList.Count >= 4 && namePartsList.Any(n => n == "&") && namePartsList[namePartsList.Count-1] != "&") || (namePartsList.Count >= 4 && namePartsList.Any(n => n == "and") && namePartsList[namePartsList.Count-1] != "and"))
        //else if ((namePartsList.Count >= 4 && (Array.IndexOf(namePartsList, "&") >= 0) && namePartsList[namePartsList.Count-1] != "&") || (namePartsList.Count >= 4 && (Array.IndexOf(namePartsList, "and") >= 0) && namePartsList[namePartsList.Count-1] != "and"))
        //source.cs(187,62): error CS1503: Argument 1: cannot convert from 'System.Collections.Generic.List<string>' to 'System.Array'
        else if ((namePartsList.Count >= 4 && namePartsList.Contains("&") && namePartsList[namePartsList.Count-1] != "&") || (namePartsList.Count >= 4 && namePartsList.Contains("and") && namePartsList[namePartsList.Count-1] != "and"))
        {
            #region Assume format: "FirstA & FirstB Last" or "FirstA and FirstB Last"
            firstName             = namePartsList[0];
            fullNameDto.FirstName = firstName;
            namePartsList.Remove(namePartsList[0]);

            firstName             = namePartsList[0];
            fullNameDto.FirstName = fullNameDto.FirstName + " " + firstName;
            namePartsList.Remove(namePartsList[0]);
            
            firstName             = namePartsList[0];
            fullNameDto.FirstName = fullNameDto.FirstName + " " + firstName;
            namePartsList.Remove(namePartsList[0]);

            if (namePartsList.Count >= 2)
            {
                //Check for possible last name prefix
                var possibleLastPrefix = namePartsList[namePartsList.Count - 2]
                                               .Replace(".", string.Empty)
                                               .Replace(",", string.Empty)
                                               .Trim()
                                               .ToLower();

                //if (lastNamePrefixes.Contains(possibleLastPrefix))
                if (Array.IndexOf(lastNamePrefixes, possibleLastPrefix) >= 0)
                {
                    //lastName = $"{namePartsList[namePartsList.Count - 2].Trim()} {namePartsList[namePartsList.Count -1].Replace(",", string.Empty).Trim()}";
                    lastName = namePartsList[namePartsList.Count - 2].Trim() + namePartsList[namePartsList.Count -1].Replace(",", string.Empty).Trim();
                    fullNameDto.LastName = lastName;

                    namePartsList.RemoveAt(namePartsList.Count - 1);
                    namePartsList.RemoveAt(namePartsList.Count - 1);
                }
                else
                {
                    lastName = namePartsList[namePartsList.Count-1].Replace(",", string.Empty).Trim();
                    fullNameDto.LastName = lastName;

                    namePartsList.RemoveAt(namePartsList.Count - 1);
                }

                middleName = string.Join(" ", namePartsList).Trim();
                fullNameDto.MiddleName = middleName;

                namePartsList.Clear();
            }
            else
            {
                if (namePartsList.Count == 1)
                {
                    lastName = namePartsList[0].Replace(",", string.Empty).Trim();
                    fullNameDto.LastName = lastName;

                    namePartsList.RemoveAt(0);
                }
                else
                {
                    var possibleLastPrefix = namePartsList[0]
                                             .Replace(".", string.Empty)
                                             .Replace(",", string.Empty)
                                             .Trim()
                                             .ToLower();

                    //if (lastNamePrefixes.Contains(possibleLastPrefix))
                    if (Array.IndexOf(lastNamePrefixes, possibleLastPrefix) >= 0)
                    {
                        //lastName = $"{namePartsList[0].Replace(",", string.Empty).Trim()} {namePartsList[namePartsList.Count-1].Replace(",", string.Empty).Trim()}";
                        lastName = namePartsList[0].Replace(",", string.Empty).Trim() + namePartsList[namePartsList.Count-1].Replace(",", string.Empty).Trim();
                        fullNameDto.LastName = lastName;

                        namePartsList.Clear();
                    }
                    else
                    {
                        namePartsList.RemoveAt(0);

                        lastName = namePartsList[namePartsList.Count-1].Replace(",", string.Empty).Trim();
                        fullNameDto.LastName = lastName;

                        namePartsList.Clear();
                    }
                }
            }
        }
        #endregion
        #endregion

        #region First part ends with a comma
        //else if (namePartsList[0].EndsWith(",") || (namePartsList.Count >= 3 && namePartsList.Any(n => n == ",") && namePartsList[namePartsList.Count-1] != ","))
        else if (namePartsList[0].EndsWith(",") || (namePartsList.Count >= 3 && namePartsList.Contains(",") && namePartsList[namePartsList.Count-1] != ","))
        {
            #region Assume format: "Last, First"
            if (namePartsList[0].EndsWith(","))
            {
                lastName             = namePartsList[0].Replace(",", string.Empty).Trim();
                fullNameDto.LastName = lastName;
                namePartsList.Remove(namePartsList[0]);

                firstName             = namePartsList[0];
                fullNameDto.FirstName = firstName;
                namePartsList.Remove(namePartsList[0]);

                if (namePartsList.Count == 0) { 
                    fullNameDto.Prefix     = textInfo.ToTitleCase(fullNameDto.Prefix);
                    fullNameDto.FirstName  = textInfo.ToTitleCase(fullNameDto.FirstName);
                    fullNameDto.MiddleName = textInfo.ToTitleCase(fullNameDto.MiddleName);
                    fullNameDto.LastName   = textInfo.ToTitleCase(fullNameDto.LastName);
                    fullNameDto.Suffix     = textInfo.ToTitleCase(fullNameDto.Suffix);
                    return fullNameDto; 
                }

                foreach (var namePart in namePartsList)
                {
                    middleName += namePart.Trim() + " ";
                }
                fullNameDto.MiddleName = middleName;

                fullNameDto.Prefix     = textInfo.ToTitleCase(fullNameDto.Prefix);
                fullNameDto.FirstName  = textInfo.ToTitleCase(fullNameDto.FirstName);
                fullNameDto.MiddleName = textInfo.ToTitleCase(fullNameDto.MiddleName);
                fullNameDto.LastName   = textInfo.ToTitleCase(fullNameDto.LastName);
                fullNameDto.Suffix     = textInfo.ToTitleCase(fullNameDto.Suffix);
                return fullNameDto;
            }
            #endregion

            #region Assume strange scenario like "Last Suffix, First"
            var indexOfComma = namePartsList.IndexOf(",");

            #region Last Name is the first thing in the list
            if (indexOfComma == 1)
            {
                namePartsList.Remove(namePartsList[indexOfComma]);

                lastName             = namePartsList[0].Replace(",", string.Empty).Trim();
                fullNameDto.LastName = lastName;
                namePartsList.Remove(namePartsList[0]);

                firstName             = namePartsList[0];
                fullNameDto.FirstName = firstName;
                namePartsList.Remove(namePartsList[0]);

                if (namePartsList.Count == 0) 
                { 
                    fullNameDto.Prefix     = textInfo.ToTitleCase(fullNameDto.Prefix);
                    fullNameDto.FirstName  = textInfo.ToTitleCase(fullNameDto.FirstName);
                    fullNameDto.MiddleName = textInfo.ToTitleCase(fullNameDto.MiddleName);
                    fullNameDto.LastName   = textInfo.ToTitleCase(fullNameDto.LastName);
                    fullNameDto.Suffix     = textInfo.ToTitleCase(fullNameDto.Suffix);
                    return fullNameDto; 
                }

                foreach (var namePart in namePartsList)
                {
                    middleName += namePart.Trim() + " ";
                }
                fullNameDto.MiddleName = middleName;

                fullNameDto.Prefix     = textInfo.ToTitleCase(fullNameDto.Prefix);
                fullNameDto.FirstName  = textInfo.ToTitleCase(fullNameDto.FirstName);
                fullNameDto.MiddleName = textInfo.ToTitleCase(fullNameDto.MiddleName);
                fullNameDto.LastName   = textInfo.ToTitleCase(fullNameDto.LastName);
                fullNameDto.Suffix     = textInfo.ToTitleCase(fullNameDto.Suffix);
                return fullNameDto;
            }
            #endregion

            #region Last Name might be a prefixed one, like "da Vinci"
            if (indexOfComma == 2)
            {
                var possibleLastPrefix = namePartsList[0]
                                                      .Replace(".", string.Empty)
                                                      .Replace(",", string.Empty)
                                                      .Trim()
                                                      .ToLower();

                //if (lastNamePrefixes.Contains(possibleLastPrefix))
                if (Array.IndexOf(lastNamePrefixes, possibleLastPrefix) >= 0)
                {
                    namePartsList.Remove(namePartsList[indexOfComma]);

                    var lastPrefix = namePartsList[0].Trim();
                    namePartsList.Remove(lastPrefix);

                    lastName             = lastPrefix + namePartsList[0].Replace(",", string.Empty).Trim();
                    fullNameDto.LastName = lastName;
                    namePartsList.Remove(namePartsList[0]);
                }
                else
                {
                    lastName = namePartsList[0].Replace(",", string.Empty).Trim();
                    namePartsList.Remove(namePartsList[0]);

                    lastName = lastName + " " + namePartsList[0].Replace(",", string.Empty).Trim();
                    namePartsList.Remove(namePartsList[0]);

                    fullNameDto.LastName = lastName;
                }

                namePartsList.Remove(",");

                firstName             = namePartsList[0];
                fullNameDto.FirstName = firstName;
                namePartsList.Remove(namePartsList[0]);

                if (namePartsList.Count == 0) 
                {
                    fullNameDto.Prefix     = textInfo.ToTitleCase(fullNameDto.Prefix);
                    fullNameDto.FirstName  = textInfo.ToTitleCase(fullNameDto.FirstName);
                    fullNameDto.MiddleName = textInfo.ToTitleCase(fullNameDto.MiddleName);
                    fullNameDto.LastName   = textInfo.ToTitleCase(fullNameDto.LastName);
                    fullNameDto.Suffix     = textInfo.ToTitleCase(fullNameDto.Suffix); 
                    return fullNameDto; 
                }

                foreach (var namePart in namePartsList)
                {
                    middleName += namePart.Trim() + " ";
                }
                fullNameDto.MiddleName = middleName;

                fullNameDto.Prefix     = textInfo.ToTitleCase(fullNameDto.Prefix);
                fullNameDto.FirstName  = textInfo.ToTitleCase(fullNameDto.FirstName);
                fullNameDto.MiddleName = textInfo.ToTitleCase(fullNameDto.MiddleName);
                fullNameDto.LastName   = textInfo.ToTitleCase(fullNameDto.LastName);
                fullNameDto.Suffix     = textInfo.ToTitleCase(fullNameDto.Suffix);
                return fullNameDto;
            }
            #endregion
            #endregion
        }
        #endregion

        #region Everything else
        else
        {
            if (namePartsList.Count >= 3)
            {
                firstName = namePartsList[0].Replace(",", string.Empty).Trim();
                fullNameDto.FirstName = firstName;
                namePartsList.RemoveAt(0);

                //Check for possible last name prefix
                var possibleLastPrefix = namePartsList[namePartsList.Count - 2]
                                               .Replace(".", string.Empty)
                                               .Replace(",", string.Empty)
                                               .Trim()
                                               .ToLower();

                //if (lastNamePrefixes.Contains(possibleLastPrefix))
                if (Array.IndexOf(lastNamePrefixes, possibleLastPrefix) >= 0)
                {
                    //lastName = $"{namePartsList[namePartsList.Count - 2].Trim()} {namePartsList[namePartsList.Count -1].Replace(",", string.Empty).Trim()}";
                    lastName = namePartsList[namePartsList.Count - 2].Trim() + namePartsList[namePartsList.Count -1].Replace(",", string.Empty).Trim();
                    fullNameDto.LastName = lastName;

                    namePartsList.RemoveAt(namePartsList.Count - 1);
                    namePartsList.RemoveAt(namePartsList.Count - 1);
                }
                else
                {
                    lastName = namePartsList[namePartsList.Count-1].Replace(",", string.Empty).Trim();
                    fullNameDto.LastName = lastName;

                    namePartsList.RemoveAt(namePartsList.Count - 1);
                }

                middleName = string.Join(" ", namePartsList).Trim();
                fullNameDto.MiddleName = middleName;

                namePartsList.Clear();
            }
            else
            {
                if (namePartsList.Count == 1)
                {
                    lastName = namePartsList[0].Replace(",", string.Empty).Trim();
                    fullNameDto.LastName = lastName;

                    namePartsList.RemoveAt(0);
                }
                else
                {
                    var possibleLastPrefix = namePartsList[0]
                                             .Replace(".", string.Empty)
                                             .Replace(",", string.Empty)
                                             .Trim()
                                             .ToLower();

                    //if (lastNamePrefixes.Contains(possibleLastPrefix))
                    if (Array.IndexOf(lastNamePrefixes, possibleLastPrefix) >= 0)
                    {
                        //lastName = $"{namePartsList[0].Replace(",", string.Empty).Trim()} {namePartsList[namePartsList.Count-1].Replace(",", string.Empty).Trim()}";
                        lastName = namePartsList[0].Replace(",", string.Empty).Trim() + namePartsList[namePartsList.Count-1].Replace(",", string.Empty).Trim();
                        fullNameDto.LastName = lastName;

                        namePartsList.Clear();
                    }
                    else
                    {
                        firstName = namePartsList[0].Replace(",", string.Empty).Trim();
                        fullNameDto.FirstName = firstName;

                        namePartsList.RemoveAt(0);

                        lastName = namePartsList[namePartsList.Count-1].Replace(",", string.Empty).Trim();
                        fullNameDto.LastName = lastName;

                        namePartsList.Clear();
                    }
                }
            }
        }
        #endregion

        namePartsList.Clear();

        fullNameDto.Prefix     = textInfo.ToTitleCase(fullNameDto.Prefix);
        fullNameDto.FirstName  = textInfo.ToTitleCase(fullNameDto.FirstName);
        fullNameDto.MiddleName = textInfo.ToTitleCase(fullNameDto.MiddleName);
        fullNameDto.LastName   = textInfo.ToTitleCase(fullNameDto.LastName);
        fullNameDto.Suffix     = textInfo.ToTitleCase(fullNameDto.Suffix);
        return fullNameDto;
    }
}

string GetStateByName(string name)
{
    switch (name.ToUpper())
    {
        case "ALABAMA":
            return "AL";

        case "ALASKA":
            return "AK";

        case "AMERICAN SAMOA":
            return "AS";

        case "ARIZONA":
            return "AZ";

        case "ARKANSAS":
            return "AR";

        case "CALIFORNIA":
            return "CA";

        case "COLORADO":
            return "CO";

        case "CONNECTICUT":
            return "CT";

        case "DELAWARE":
            return "DE";

        case "DISTRICT OF COLUMBIA":
            return "DC";

        case "FEDERATED STATES OF MICRONESIA":
            return "FM";

        case "FLORIDA":
            return "FL";

        case "GEORGIA":
            return "GA";

        case "GUAM":
            return "GU";

        case "HAWAII":
            return "HI";

        case "IDAHO":
            return "ID";

        case "ILLINOIS":
            return "IL";

        case "INDIANA":
            return "IN";

        case "IOWA":
            return "IA";

        case "KANSAS":
            return "KS";

        case "KENTUCKY":
            return "KY";

        case "LOUISIANA":
            return "LA";

        case "MAINE":
            return "ME";

        case "MARSHALL ISLANDS":
            return "MH";

        case "MARYLAND":
            return "MD";

        case "MASSACHUSETTS":
            return "MA";

        case "MICHIGAN":
            return "MI";

        case "MINNESOTA":
            return "MN";

        case "MISSISSIPPI":
            return "MS";

        case "MISSOURI":
            return "MO";

        case "MONTANA":
            return "MT";

        case "NEBRASKA":
            return "NE";

        case "NEVADA":
            return "NV";

        case "NEW HAMPSHIRE":
            return "NH";

        case "NEW JERSEY":
            return "NJ";

        case "NEW MEXICO":
            return "NM";

        case "NEW YORK":
            return "NY";

        case "NORTH CAROLINA":
            return "NC";

        case "NORTH DAKOTA":
            return "ND";

        case "NORTHERN MARIANA ISLANDS":
            return "MP";

        case "OHIO":
            return "OH";

        case "OKLAHOMA":
            return "OK";

        case "OREGON":
            return "OR";

        case "PALAU":
            return "PW";

        case "PENNSYLVANIA":
            return "PA";

        case "PUERTO RICO":
            return "PR";

        case "RHODE ISLAND":
            return "RI";

        case "SOUTH CAROLINA":
            return "SC";

        case "SOUTH DAKOTA":
            return "SD";

        case "TENNESSEE":
            return "TN";

        case "TEXAS":
            return "TX";

        case "UTAH":
            return "UT";

        case "VERMONT":
            return "VT";

        case "VIRGIN ISLANDS":
            return "VI";

        case "VIRGINIA":
            return "VA";

        case "WASHINGTON":
            return "WA";

        case "WEST VIRGINIA":
            return "WV";

        case "WISCONSIN":
            return "WI";

        case "WYOMING":
            return "WY";

        default:
            return "name";
    }
}