Split last name and suffix in field

I have a last name field that includes suffixes, and I need to split the field into a last name field and a suffix field. I have tried charindex, but am having a hard time with the syntax. Samples of the data are:

Miller 
Wilson Jr
Hearne Sr
Horner III
Bond jr
Jones-Alexander
Brown ii
Rodriguez Maldonado
Appleby

I am trying to find a reliable select statement in SQL Server to pull this data into another table with the last name and suffix separated.

Thank you for any help.

Edit Additional Info posted as answer

If there is no suffix, I'd like to get the last name into a LastName field, and leave the suffix as Null.

Fortunately, there are no first names or titles in this field, so I won't have to worry about those, just the 开发者_Go百科 II, III, IV, Jr , Sr, two part last names,

---

You should be able to use a CASE statement that checks for the existence of the name suffices you are seeing in your data.

CASE WHEN POSITION(' Jr' IN LastName) > 0 
     THEN SUBSTRING(LastName FROM POSITION(' Jr' IN LastName) FOR 2)
     WHEN POSITION(' III' IN LastName) > 0
     ....
END

It certainly won't catch everything and you may have to continually tweak it. Your other option would be to introduce a Data Quality tool that can parse name data using known lists of given names and surnames coupled with suffices and titles. Don't forget you may encounter John M. Smith III, MD. How are your going to handle that?

---

Thank you for your help. I ended up using a Case Statement to get what I needed. It seems a bit crude, but looks like it is reliable for what I need:

CASE
  WHEN (mytable.L_NAME like '% Jr') Then SUBSTRING(mytable.L_NAME, 1,CHARINDEX(' ', mytable.L_NAME) - 1)
  WHEN (mytable.L_NAME like '% Sr') Then SUBSTRING(mytable.L_NAME, 1,CHARINDEX(' ', mytable.L_NAME) - 1)
  WHEN (mytable.L_NAME like '% II') Then SUBSTRING(mytable.L_NAME, 1,CHARINDEX(' ', mytable.L_NAME) - 1)
  WHEN (mytable.L_NAME like '% III') Then SUBSTRING(mytable.L_NAME, 1,CHARINDEX(' ', mytable.L_NAME) - 1)
  WHEN (mytable.L_NAME like '% IV') Then SUBSTRING(mytable.L_NAME, 1,CHARINDEX(' ', mytable.L_NAME) - 1)
  WHEN (mytable.L_NAME like '% V') Then SUBSTRING(mytable.L_NAME, 1,CHARINDEX(' ', mytable.L_NAME) - 1)
  WHEN (mytable.L_NAME like '% VI') Then SUBSTRING(mytable.L_NAME, 1,CHARINDEX(' ', mytable.L_NAME) - 1)
  ELSE mytable.L_NAME
END
AS LastName,

CASE
  WHEN (mytable.L_NAME like '% Jr') Then 'Jr'
  WHEN (mytable.L_NAME like '% Sr') Then 'Sr'
  WHEN (mytable.L_NAME like '% II') Then 'II'
  WHEN (mytable.L_NAME like '% III') Then 'III'
  WHEN (mytable.L_NAME like '% IV') Then 'IV'
  WHEN (mytable.L_NAME like '% V') Then 'V'
  WHEN (mytable.L_NAME like '% VI') Then 'VI'
  ELSE NULL
END
AS Suffix,