Native SQL to Parse Prefix First Middle Last Suffix from full Name without platform dependent functions

Parse a full name in the format, prefix first middle last suffix. Not all parts may be included. Most commonly there may be no middle name, prefix, or suffix. I want to be able to accomplish this in SQL not using any platform dependent functions so it can be a platform independent bridge. I tried some code but the suffix Jr. gets stuffed in with the last name. It may be that a web service for data quality or machine learning like Melissa would work better. The example includes data. SELECT FIRST_NAME.INPUT_DATA ,[login to view URL] ,FIRST_NAME.FIRST_NAME ,CASE WHEN 0 = CHARINDEX(' ',[login to view URL]) THEN NULL --no more spaces found, consider remaining to be last name ELSE SUBSTRING( [login to view URL] ,1 ,CHARINDEX(' ',[login to view URL])-1 ) END AS MIDDLE_NAME ,SUBSTRING( [login to view URL] ,1 + CHARINDEX(' ',[login to view URL]) ,LEN([login to view URL]) ) AS LAST_NAME FROM ( SELECT [login to view URL] ,CASE WHEN 0 = CHARINDEX(' ',[login to view URL]) THEN [login to view URL] --no space found, return the entire string ELSE SUBSTRING( [login to view URL] ,1 ,CHARINDEX(' ',[login to view URL])-1 ) END AS FIRST_NAME ,CASE WHEN 0 = CHARINDEX(' ',[login to view URL]) THEN NULL --no spaces found, consider to be first name ELSE SUBSTRING( [login to view URL] ,CHARINDEX(' ',[login to view URL])+1 ,LEN([login to view URL]) ) END AS REMAINING ,PREFIX.INPUT_DATA FROM ( SELECT --CLEAN_DATA --if first three characters match list, --parse as a "PREFIX". else return NULL for PREFIX. CASE WHEN SUBSTRING(CLEAN_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS') THEN LTRIM(RTRIM(SUBSTRING(CLEAN_DATA.FULL_NAME,1,3))) ELSE NULL END AS PREFIX ,CASE WHEN SUBSTRING(CLEAN_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS') THEN LTRIM(RTRIM(SUBSTRING(CLEAN_DATA.FULL_NAME,4,LEN(CLEAN_DATA.FULL_NAME)))) ELSE LTRIM(RTRIM(CLEAN_DATA.FULL_NAME)) END AS REMAINING ,CLEAN_DATA.INPUT_DATA FROM ( SELECT --trim leading & trailing spaces to prepare for processing --replace extra spaces in name REPLACE(REPLACE(LTRIM(RTRIM(FULL_NAME)),' ',' '),' ',' ') AS FULL_NAME ,FULL_NAME AS INPUT_DATA FROM ( --test with test data, or table --table --SELECT CONTACT AS FULL_NAME --FROM CONTACT --test data --/* SELECT 'Andy D Where' AS FULL_NAME UNION SELECT 'Cathy T Landers' AS FULL_NAME UNION SELECT 'Ms Annie Wint There' AS FULL_NAME UNION SELECT 'Frank Fields' AS FULL_NAME UNION SELECT 'Howdy U Pokes Jr.' AS FULL_NAME --*/ ) SOURCE_DATA ) CLEAN_DATA ) PREFIX ) FIRST_NAME