SQL salutation, forename, surname splitter

SELECT
  FIRST_NAME.ORIGINAL_INPUT_DATA
 ,FIRST_NAME.TITLE
 ,FIRST_NAME.FIRST_NAME
 ,CASE WHEN 0 = CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
       THEN NULL  --no more spaces?  assume rest is the last name
       ELSE SUBSTRING(
                       FIRST_NAME.REST_OF_NAME
                      ,1
                      ,CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)-1
                     )
       END AS MIDDLE_NAME
 ,SUBSTRING(
             FIRST_NAME.REST_OF_NAME
            ,1 + CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
            ,LEN(FIRST_NAME.REST_OF_NAME)
           ) AS LAST_NAME
FROM
  (
  SELECT
    TITLE.TITLE
   ,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)
         THEN TITLE.REST_OF_NAME --No space? return the whole thing
         ELSE SUBSTRING(
                         TITLE.REST_OF_NAME
                        ,1
                        ,CHARINDEX(' ',TITLE.REST_OF_NAME)-1
                       )
    END AS FIRST_NAME
   ,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)
         THEN NULL  --no spaces @ all?  then 1st name is all we have
         ELSE SUBSTRING(
                         TITLE.REST_OF_NAME
                        ,CHARINDEX(' ',TITLE.REST_OF_NAME)+1
                        ,LEN(TITLE.REST_OF_NAME)
                       )
    END AS REST_OF_NAME
   ,TITLE.ORIGINAL_INPUT_DATA
  FROM
    (
    SELECT
      --if the first three characters are in this list,
      --then pull it as a "title".  otherwise return NULL for title.
      CASE /*WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
           THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,1,3)))*/
                WHEN (SUBSTRING(TEST_DATA.FULL_NAME,3,1) IN (' ','.') AND SUBSTRING(TEST_DATA.FULL_NAME,1,2) IN ('MR','MS','DR','FR'))
                    THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,1,2)))
                WHEN (SUBSTRING(TEST_DATA.FULL_NAME,4,1) IN (' ','.') AND SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MRS','LRD','SIR'))
                    THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,1,3)))
                WHEN (SUBSTRING(TEST_DATA.FULL_NAME,5,1) IN (' ','.') AND SUBSTRING(TEST_DATA.FULL_NAME,1,4) IN ('LORD','LADY','MISS','PROF'))
                    THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,1,4)))
           ELSE NULL
           END AS TITLE
      --if you change the list, don't forget to change it here, too.
      --so much for the DRY prinicple...
     ,CASE/* WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
           THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,4,LEN(TEST_DATA.FULL_NAME))))*/
                WHEN (SUBSTRING(TEST_DATA.FULL_NAME,3,1) IN (' ','.') AND SUBSTRING(TEST_DATA.FULL_NAME,1,2) IN ('MR','MS','DR','FR'))
                    THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,4,LEN(TEST_DATA.FULL_NAME))))
                WHEN (SUBSTRING(TEST_DATA.FULL_NAME,4,1) IN (' ','.') AND SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MRS','LRD','SIR'))
                    THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,5,LEN(TEST_DATA.FULL_NAME))))
                WHEN (SUBSTRING(TEST_DATA.FULL_NAME,5,1) IN (' ','.') AND SUBSTRING(TEST_DATA.FULL_NAME,1,4) IN ('LORD','LADY','MISS','PROF'))
                    THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,6,LEN(TEST_DATA.FULL_NAME))))
           ELSE LTRIM(RTRIM(TEST_DATA.FULL_NAME))
           END AS REST_OF_NAME
     ,TEST_DATA.ORIGINAL_INPUT_DATA
    FROM
      (
      SELECT
        --trim leading & trailing spaces before trying to process
        --disallow extra spaces *within* the name
        REPLACE(REPLACE(LTRIM(RTRIM(FULL_NAME)),'  ',' '),'  ',' ') AS FULL_NAME
       ,FULL_NAME AS ORIGINAL_INPUT_DATA
      FROM
        (
        --if you use this, then replace the following
        --block with your actual table
              SELECT 'GEORGE W BUSH' AS FULL_NAME
        UNION SELECT 'SUSAN B ANTHONY' AS FULL_NAME
        UNION SELECT 'ALEXANDER HAMILTON' AS FULL_NAME
        UNION SELECT 'OSAMA BIN LADEN JR' AS FULL_NAME
        UNION SELECT 'MARTIN J VAN BUREN SENIOR III' AS FULL_NAME
        UNION SELECT 'TOMMY' AS FULL_NAME
        UNION SELECT 'BILLY' AS FULL_NAME
        UNION SELECT NULL AS FULL_NAME
        UNION SELECT ' ' AS FULL_NAME
        UNION SELECT '    JOHN  JACOB     SMITH' AS FULL_NAME
        UNION SELECT ' DR  SANJAY       GUPTA' AS FULL_NAME
        UNION SELECT ' DR.  SANJAY       GUPTA' AS FULL_NAME
        UNION SELECT 'DR JOHN S HOPKINS' AS FULL_NAME
        UNION SELECT ' MRS  SUSAN ADAMS' AS FULL_NAME
        UNION SELECT ' MS AUGUSTA  ADA   KING ' AS FULL_NAME
        UNION SELECT ' MS. AUGUSTA  ADA   KING ' AS FULL_NAME
        UNION SELECT ' LADY AUGUSTA  ADA   KING ' AS FULL_NAME
        ) RAW_DATA
      ) TEST_DATA
    ) TITLE
  ) FIRST_NAME