SQL salutation, forename, surname splitter FUNCTION

CREATE FUNCTION NameSplitter
    (
        @name nvarchar(100)
    )
RETURNS
    @names TABLE(
        name nvarchar(100),
        salutation nvarchar(100),
        forename nvarchar(100),
        middle_name nvarchar(100),
        surname nvarchar(100)
    )
AS BEGIN

    INSERT INTO @names
    SELECT
      FIRST_NAME.ORIGINAL_INPUT_DATA name
     ,FIRST_NAME.SALUTATION salutation
     ,FIRST_NAME.FIRST_NAME forename
     ,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 surname
    FROM
      (
      SELECT
        TITLE.SALUTATION
       ,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,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 SALUTATION
          --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,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
            (
                SELECT @name AS FULL_NAME
            ) RAW_DATA
          ) TEST_DATA

        ) TITLE
      ) FIRST_NAME

      RETURN
END
GO

-- TESTING
SELECT * FROM dbo.NameSplitter('Dr. Mark J Smith')