Using charindex to correct name with two commas

I need to write a SQL Query that updates a field with a proper version of their name. Currently, we store employees as Last Name, First Name, and will be implementing email variables so we can say “Hello First Name Last Name”

I currently have a working query that corrects where there is a single comma, but I need a query that works the same way, but when their are two commas such as “Doe, Jr, John” to appear as “John Doe, Jr”

UPDATE A_EMPLOYEE SET DISPLAY_NAME = CONCAT((SUBSTRING(
    LAST_NAME,
    CHARINDEX (', ', LAST_NAME) + 2,
    LEN (LAST_NAME)
)),' ',(SUBSTRING(
    LAST_NAME,
    0,
    CHARINDEX (', ', LAST_NAME, 0)
))) WHERE EMPLOYEE_ID = @var (variable set by system)

Answer

It’s going to be messy, so you will need lots of test samples, but here you go. Use REVERSE() to find the last comma, then use that location to parse the first and last.

--DECLARE @fullname VARCHAR(100) = 'Doe, Jr, John'
--DECLARE @fullname VARCHAR(100) = 'Smith, Mike'
DECLARE @fullname VARCHAR(100) = 'Johnson, Sr, John'


SELECT @fullname AS raw_name, 
LTRIM(RTRIM(RIGHT(@fullname, CHARINDEX(',', REVERSE(@fullname), 0)-1))) AS first_name, 
LTRIM(RTRIM(LEFT(@fullname, LEN(@fullname)-CHARINDEX(',', REVERSE(@fullname), 0)))) AS last_name, 
LTRIM(RTRIM(RIGHT(@fullname, CHARINDEX(',', REVERSE(@fullname), 0)-1))) + ' ' + 
LTRIM(RTRIM(LEFT(@fullname, LEN(@fullname)-CHARINDEX(',', REVERSE(@fullname), 0)))) AS full_name