Parsing Names From a Delimited String in VDM

Trouble Viewing Images? Right-click on any image and select "Open in new tab" to view a larger version. You can also zoom in using Ctrl + Mouse Wheel for easier readability.

Article Goal

Provide a dynamic and reliable method to parse First Name, Middle Initial, and Last Name from a single comma-delimited string (e.g., "DOE,JOHN,A") using VDM expressions—whether or not a Middle Initial is present. This article outlines how to extract individual name components from a string in the format LASTNAME,FIRSTNAME,M. This is useful for ensuring clean data presentation or for populating separate name fields in reports or exports.

Why Parse Name Strings?

Many datasets store names in a single delimited string, which can make it difficult to use or filter by first, middle, or last names independently. Parsing them into separate components enables:

• Cleaner presentation

• Improved filtering and sorting

• Consistent report formatting

• Compatibility with systems requiring distinct name fields

Creating Expressions to Parse Name Components

The following expressions support both formats:

• With a middle initial: DOE,JOHN,A

• Without a middle initial: SMITH,JANE

Extract Last Name

Extracts the string segment before the first comma:

Substring([Account_Name1], 0, CharIndex(',', [Account_Name1]))

Example Output:

• DOE from DOE,JOHN,A

• SMITH from SMITH,JANE

Extract First Name (Flexible Logic)

Uses padding to handle missing middle initials without skipping the first name:

Substring(
    [Account_Name1] + ',_',
    CharIndex(',', [Account_Name1] + ',_') + 1,
    CharIndex(',', [Account_Name1] + ',_', CharIndex(',', [Account_Name1] + ',_') + 1) 
    - CharIndex(',', [Account_Name1] + ',_') - 1
)

Example Output:

• JOHN from DOE,JOHN,A

• JANE from SMITH,JANE

Tip: The appended ',_' ensures the second comma always exists, even if the middle initial is missing.

Extract Middle Initial (Conditionally)

Uses a conditional logic to count the commas and extract the middle initial only if present:

Iif(
    Len([Account_Name1]) - Len(Replace([Account_Name1], ',', '')) = 2,
    Substring(
        [Account_Name1],
        CharIndex(',', [Account_Name1], CharIndex(',', [Account_Name1]) + 1) + 1,
        1
    ),
    ''
)

Example Output:

• A from DOE,JOHN,A

• [blank] from SMITH,JANE

Note: If the number of commas is less than 2, the middle initial will return as an empty string.

Additional Tips

• Padding: Appending extra characters (e.g., ',_') is a safe way to avoid missing delimiter errors.

• Logic Branching: Use Len() and Replace() to count the number of delimiters to determine if certain segments (like middle initial) exist.

• Dynamic Flexibility: These expressions make your parsing resilient to inconsistent input lengths.

Article Summary

This article outlines a robust method to parse names stored in a single field using VDM expressions. It walks through how to extract the Last Name, First Name, and Middle Initial, even if the middle initial is missing. By leveraging string functions like Substring, CharIndex, Len, and Replace, the approach ensures reliable output without manual preprocessing or cleanup.