SQL: parse the first, middle and last name from a fullname field

Unless you have very, very well-behaved data, this is a non-trivial challenge. A naive approach would be to tokenize on whitespace and assume that a three-token result is [first, middle, last] and a two-token result is [first, last], but you're going to have to deal with multi-word surnames (e.g. "Van Buren") and multiple middle names.

Subject to the caveats that have already been raised regarding spaces in names and other anomalies, the following code will at least handle 98% of names. (Note: messy SQL because I don't have a regex option in the database I use.)

**Warning: messy SQL follows:

create table parsname (fullname char(50), name1 char(30), name2 char(30), name3 char(30), name4 char(40));
insert into parsname (fullname) select fullname from ImportTable;
update parsname set name1 = substring(fullname, 1, locate(' ', fullname)),
 fullname = ltrim(substring(fullname, locate(' ', fullname), length(fullname)))
 where locate(' ', rtrim(fullname)) > 0;
update parsname set name2 = substring(fullname, 1, locate(' ', fullname)),
 fullname = ltrim(substring(fullname, locate(' ', fullname), length(fullname)))
 where locate(' ', rtrim(fullname)) > 0;
update parsname set name3 = substring(fullname, 1, locate(' ', fullname)),
 fullname = ltrim(substring(fullname, locate(' ', fullname), length(fullname)))
 where locate(' ', rtrim(fullname)) > 0;
update parsname set name4 = substring(fullname, 1, locate(' ', fullname)),
 fullname = ltrim(substring(fullname, locate(' ', fullname), length(fullname)))
 where locate(' ', rtrim(fullname)) > 0;
// fullname now contains the last word in the string.
select fullname as FirstName, '' as MiddleName, '' as LastName from parsname where fullname is not null and name1 is null and name2 is null
union all
select name1 as FirstName, name2 as MiddleName, fullname as LastName from parsname where name1 is not null and name3 is null

The code works by creating a temporary table (parsname) and tokenizing the fullname by spaces. Any names ending up with values in name3 or name4 are non-conforming and will need to be dealt with differently.