Matching substring to table column
I will suggest to have a table valued function. This function will accept your string as an input parameter and then return a table by splitting the inputs separated by ";" in different rows with an index ID. You can use this function internally with you logic and determine the MAX(Us) from mapping table. Below is the sample example for the function.

CREATE FUNCTION [dbo].[funcSplit] ( @string VARCHAR(MAX) )
RETURNS
 @List TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(';', @string ) &gt; 0
 BEGIN
  SELECT @pos  = CHARINDEX(';', @string )
  SELECT @name = SUBSTRING(@string , 1, @pos-1)

  INSERT INTO @List
  SELECT @name

  SELECT @string = SUBSTRING(@string , @pos+1, LEN(@string )-@pos)
 END

 INSERT INTO @List
 SELECT @string 

 RETURN
END


Once implemented, use this function as below in your own logic to determine the roles.

SELECT MAX(us)
FROM YourMappingTable AS A
     INNER JOIN funcSplit('MA;LA') AS B
     ON A.Them = B.Name


Only thing is remaining is to include an exception for the NA or "Input Does not exists" case which can be easily handled.