Try using my
splitter function
DECLARE @names TABLE (nameString NVARCHAR(100))
INSERT INTO @names (nameString) VALUES
('Jean-Luc Picard'),('James Tiberious Kirk'),('William Thomas Riker'),('Rachel Garret'),('That Guy With Five Names')
;WITH singleNames AS (
SELECT *
FROM @names
CROSS APPLY dbo.splitter(nameString,' ')
), consNames AS (
SELECT *, (SELECT MIN(ID) FROM singleNames WHERE nameString = n.nameString) AS MIN, (SELECT MAX(ID) FROM singleNames WHERE nameString = n.nameString) AS MAX
FROM singleNames n
WHERE ID NOT IN ((SELECT MIN(ID) FROM singleNames WHERE nameString = n.nameString),(SELECT MAX(ID) FROM singleNames WHERE nameString = n.nameString))
), otherNames AS (
SELECT nameString, value, ID, min, max
FROM consNames n
WHERE ID - 1 = min
UNION ALL
SELECT a.nameString, a.value + ' ' + r.value, r.ID, a.min, a.max
FROM otherNames a
INNER JOIN consNames r
ON a.nameString = r.nameString
AND a.ID + 1 = r.id
AND r.ID < a. max
)
SELECT f.value AS firstName, l.value AS lastName, o.value AS otherNames, f.value + COALESCE(' '+o.value,'')+' '+l.value AS fullName
FROM @names n
INNER JOIN singleNames f
ON n.nameString = f.nameString
AND f.ID = (SELECT MIN(ID) FROM singleNames WHERE nameString = n.nameString)
INNER JOIN singleNames l
ON n.nameString = l.nameString
AND l.ID = (SELECT MAX(ID) FROM singleNames WHERE nameString = n.nameString)
LEFT OUTER JOIN otherNames o
ON n.nameString = o.nameString
AND o.ID = (SELECT MAX(ID) FROM otherNames WHERE nameString = n.nameString)