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')