Separate “Mr John Joe Smith” into “Mr” “John” “Joe” “Smith”

A file supplied by a Client has the following structure:

FullName
Mr John Joe Smith

They would like this transformed to the following:

Salutation	FirstName	MiddleName	LastName
Mr	John	Joe	Smith

I’ve looked into this and I’ve tried the following:

SELECT        substring(FullName,0,charindex(' ',Fullname)) As Salutation, substring(FullName,charindex(' ',Fullname)+1,len(fullname)) As FirstName
FROM            dbo.Individuals

This produces:

Salutation	FirstName
Mr	John Joe Smith

I’m struggling to separate it further, is this the best way of doing it or is there a better way?

Any help would be greatly appreciated, thanks.

Answer

As long as there’s always the same four elements in the same order…

select  parsename(replace('Mr John Joe Smith',' ','.'),4) [Salutation],
        parsename(replace('Mr John Joe Smith',' ','.'),3) [FirstName],
        parsename(replace('Mr John Joe Smith',' ','.'),2) [MiddleName],
        parsename(replace('Mr John Joe Smith',' ','.'),1) [LastName]