SQL matching specific pattern value

Question

I want to join tables Department and Employee and display the results by matching text value of column DEPART and NAME.

DEPART has a pattern DEPARTMENT-NAME so it is possible to just match -NAME with values of column NAME?

Table Department:

ID	DEPART	DESC
A00	MARKETING-BOB	MARKETING
A01	MARKETING-BOB	MARKETING
A02	MARKETING-BOB	MARKETING
A03	MARKETING-BOB	MARKETING

Table Employee:

ID	NAME
A00	STUART, BOB
A01	STUART, BOB
A02	STUART, BOB
A03	STUART, BOB
A00	ELANE, CASTLE JOE
A01	ELANE, CASTLE JOE
A02	ELANE, CASTLE JOE
A03	ELANE, CASTLE JOE

Result :

ID	DEPART	NAME
A00	MARKETING-BOB	STUART, BOB
A01	MARKETING-BOB	STUART, BOB
A02	MARKETING-BOB	STUART, BOB
A03	MARKETING-BOB	STUART, BOB

Answer 1

Checking if the name of the employee is like the last part of the department's name.

SELECT 
  dep.ID
, dep.DEPART
, emp.NAME
FROM Employee emp
JOIN Department dep 
  ON dep.ID = emp.ID
 AND emp.NAME LIKE '%, '+RIGHT(dep.DEPART,PATINDEX('%_-%',REVERSE(dep.DEPART)))

ID	DEPART	NAME
A00	MARKETING-BOB	STUART, BOB
A01	MARKETING-BOB	STUART, BOB
A02	MARKETING-BOB	STUART, BOB
A03	MARKETING-BOB	STUART, BOB

Answer 2

There's a high risk when you are doing fuzzy matching on potentially ambiguous data (is there more than one Bob?), but the following is a start:

DECLARE @Department TABLE(ID VARCHAR(10), DEPART VARCHAR(100), [DESC] VARCHAR(100))
INSERT @Department
VALUES
    ('A00', 'MARKETING-BOB', 'MARKETING'),
    ('A01', 'MARKETING-BOB', 'MARKETING'),
    ('A02', 'MARKETING-BOB', 'MARKETING'),
    ('A03', 'MARKETING-BOB', 'MARKETING'),
    ('A02', 'ACCOUNTING-JOE', 'ACCOUNTING'),
    ('A03', 'ACCOUNTING-CASTLE JOE', 'FINANCE')

DECLARE @Employee TABLE(ID VARCHAR(10), NAME VARCHAR(100))
INSERT @Employee
VALUES
    ('A00', 'STUART, BOB'),
    ('A01', 'STUART, BOB'),
    ('A02', 'STUART, BOB'),
    ('A03', 'STUART, BOB'),
    ('A00', 'ELANE, CASTLE JOE'),
    ('A01', 'ELANE, CASTLE JOE'),
    ('A02', 'ELANE, CASTLE JOE'),
    ('A03', 'ELANE, CASTLE JOE')

SELECT D.*, E.NAME
FROM @Department D
--CROSS APPLY (SELECT Name = REPLACE(D.DEPART, 'MARKETING-', '')) DN
--CROSS APPLY (SELECT Name = REPLACE(D.DEPART, D.[DESC] + '-', '')) DN
CROSS APPLY (SELECT Name = STUFF(D.DEPART, 1, NULLIF(CHARINDEX('-', D.DEPART), -1), '')) DN -- More generalized
JOIN @Employee E ON E.ID = D.ID
    --AND E.NAME LIKE ('% ' + DN.Name) -- Match tail following space
    AND E.NAME LIKE ('%, ' + DN.Name) -- Match tail following comma-space

Any of the cross apply extracts the name to be matched, and the LIKE attempts a pattern match the tail end of what I presume is '<last>, <first> <optional-middle>'.