Well, that changes the game.

My solution:
WITH SC AS(
  SELECT RIGHT(N.Name, LEN(N.Name) - (PATINDEX('% AND %', N.Name) + 4)) AS SecondConsumer
  FROM #NAME N
  WHERE N.Name LIKE '% AND %'),
SCS AS (
  SELECT LEFT(SC.SecondConsumer, CHARINDEX(' ',SC.SecondConsumer) - 1) AS FirstName,
    RIGHT(SC.SecondConsumer, CHARINDEX(' ',REVERSE(SC.SecondConsumer))-1) AS Surname,
    *
  FROM SC)
SELECT FirstName,
   CASE WHEN LEN(SCS.Firstname) + LEN(SCS.Surname) + 1 < LEN(SCS.SecondConsumer)
    THEN SUBSTRING(SCS.SecondConsumer, LEN(SCS.Firstname) +2 , LEN(SCS.SecondConsumer) - (LEN(SCS.Firstname) + LEN(SCS.Surname) + 1) - 1) END AS MiddleName,
   SCS.Surname,
   SecondConsumer
FROM SCS;

@luis, I would not be surprised if this is Vendor based.

EDIT: Slight amendment due to leading whitespace on middlename.