How do I parse the first, middle, and last name out of a fullname field with SQL?
I need to try to match up on names that are not a direct match on full name. I'd like to be able to take the full name field and break it up into first, middle and last name.
The data does not include any prefixes or suffixes. The middle name is optional. The data is formatted 'First Middle Last'.
I'm interested in some practical solutions to get me 90% of the way there. As it has been stated, this is a complex problem, so I'll handle special cases individually.
Best Answer
Here is a self-contained example, with easily manipulated test data.
With this example, if you have a name with more than three parts, then all the "extra" stuff will get put in the LAST_NAME field. An exception is made for specific strings that are identified as "titles", such as "DR", "MRS", and "MR".
If the middle name is missing, then you just get FIRST_NAME and LAST_NAME (MIDDLE_NAME will be NULL).
You could smash it into a giant nested blob of SUBSTRINGs, but readability is hard enough as it is when you do this in SQL.
Edit-- Handle the following special cases:
1 - The NAME field is NULL
2 - The NAME field contains leading / trailing spaces
3 - The NAME field has > 1 consecutive space within the name
4 - The NAME field contains ONLY the first name
5 - Include the original full name in the final output as a separate column, for readability
6 - Handle a specific list of prefixes as a separate "title" column