LNAME                    FirstAndMiddle           MNAME                    LNAME
------------------------ ------------------------ ------------------------ ------------------------
DOE                      JOHN F                    F                       JOHN 
JONES                    MARY ALICE                ALICE                   MARY 
SMITH                    HAROLD                                            HAROLD
RUCKUS                   BILLY JOE JIM BOB         JOE JIM BOB             BILLY 

SELECT 'DOE/JOHN F' AS FULLNAME INTO #TMP UNION ALL
SELECT 'JONES/MARY ALICE' UNION ALL
SELECT 'SMITH/HAROLD' UNION ALL
SELECT 'RUCKUS/BILLY JOE JIM BOB'


SELECT
--assumes the slash is ALWAYS in the data:
  SUBSTRING(FULLNAME,1,CHARINDEX('/',FULLNAME) -1) AS LNAME, --minus one to remove the slash
  SUBSTRING(FULLNAME,CHARINDEX('/',FULLNAME)+ 1,30) AS FirstAndMiddle, --add one to remove the slash
  CASE 
    --if there is a space, ASSUME the second word is a middle name, and not a two part first name
    WHEN CHARINDEX(' ',SUBSTRING(FULLNAME,CHARINDEX('/',FULLNAME)+ 1,30)) > 1 
    THEN SUBSTRING(SUBSTRING(FULLNAME,CHARINDEX('/',FULLNAME)+ 1,30),CHARINDEX(' ',SUBSTRING(FULLNAME,CHARINDEX('/',FULLNAME)+ 1,30)),30)
    --else no middle name
    ELSE ''
  END AS MNAME,
  CASE 
    --if there is a space, ASSUME the second word is a first name,
    WHEN CHARINDEX(' ',SUBSTRING(FULLNAME,CHARINDEX('/',FULLNAME)+ 1,30)) > 1 
    THEN SUBSTRING(FULLNAME,CHARINDEX('/',FULLNAME)+ 1,(CHARINDEX(' ',FULLNAME)-1) - CHARINDEX('/',FULLNAME)+ 1)
    --else whole thing is first name
    ELSE SUBSTRING(FULLNAME,CHARINDEX('/',FULLNAME)+ 1,30)
  END AS LNAME
  from #TMP