Splitting of string from right side or in reverse order

WITH test
AS (
 SELECT Employee_no
  ,Project_code
  ,cast(CASE 
    WHEN charindex(',', reverse(Project_code)) > 0
     THEN substring(Project_code, len(Project_code) - charindex(',', reverse(Project_code)) + 2, CHARINDEX(',', Project_code))
    ELSE Project_code
    END AS VARCHAR(50)) Project_code1
  ,stuff(Project_code, len(Project_code) - charindex(',', reverse(Project_code)) + 1, charindex(',', (Project_code)), '') dta
  ,charindex('', Project_code) chr
 FROM sample_data_emp
 WHERE Employee_no IS NOT NULL
 
 UNION ALL
 
 SELECT Employee_no
  ,Project_code
  ,cast(CASE 
    WHEN charindex(',', reverse(dta)) > 0
     THEN substring(dta, len(dta) - charindex(',', reverse(dta)) + 2, CHARINDEX(',', dta))
    ELSE dta
    END AS VARCHAR(50)) dta
  ,stuff(dta, len(dta) - charindex(',', reverse(dta)) + 1, charindex(',', (dta)) + 1, '') dta
  ,charindex('', dta) chr
 FROM test
 WHERE dta <> ''
 )
SELECT Employee_no
 ,Project_code
 ,Project_code1
FROM test