Table Overview

Matter Participants are contacts that are related to Matters.

They exist in the table: __M_Matters_Participating_Entities

They have three primary fields:

How Data Becomes Messy

Some systems treat a Role as a pick-list.  This ensures that all roles come from a well-managed list.  Apps like these seldom have problems.

However, in some systems, the Role is a free text field allowing any kind of input.  Over time, this tends to result in multiple spellings/variants of the same label.  For example, such a system may have the following roles:

Father
Fahter (A typo of 'Father')
Dad    (Another term for 'Father')

When restor these fields, unifying all these terms will provide a better experience for the firm.
 

Identifying Data Needing Cleanup

Before fixing anything, you should run this query to see a list of all used roles:

SELECT DISTINCT
    Final_Participant_Type,
    Final_Role
FROM
    __M_Matters_Participating_Entities
ORDER BY
    Final_Participant_Type,
    Final_Participant_Role

When you do, you will see results like this.

You should then visually review the list and determine which items you want to merge.  Then you should run a script like this for each item you want to consolidate:

--The client has multiple variants of the 'Father' role.
--Unify them.
UPDATE 
    __M_Matters_Participating_Entities
SET 
    --This is our new value
    Final_Participant_Role = 'Father'
WHERE 1=1
    --Only do this replacement on Contacts.
    AND Final_Participant_Type = '__M_Contacts'
    --These are our old values.
    AND Final_Participant_Role IN ('Dad', 'Fahter')

Once you have done your replacements, you should repeat this process to ensure all records are cleaned up to your satisfaction.