Your question isn't very clear to me (would help if you showed the output you were after), but this seems like what you are after:
DECLARE @Mapping table (MapName varchar(100), SourceValue varchar(100), TargetValue varchar(100))
DECLARE @StudentCourses table (ID int, CourseDate datetime, CourseName varchar(100))
INSERT INTO @Mapping VALUES('Class_Taken', 'Science', 'General')
INSERT INTO @Mapping VALUES('Class_Taken', '1345', 'General')
INSERT INTO @Mapping VALUES('Class_Taken', 'Math', 'General')
INSERT INTO @Mapping VALUES('Class_Taken', 'Reading', 'General')
INSERT INTO @Mapping VALUES('Class_Taken', '23041', 'General')
INSERT INTO @StudentCourses VALUES (111111, GetDate(), 'Algebra')
INSERT INTO @StudentCourses VALUES (311112, GetDate(), 'Science 101')
INSERT INTO @StudentCourses VALUES (131211, GetDate(), 'English')
INSERT INTO @StudentCourses VALUES (112111, GetDate(), 'Math Basics')
INSERT INTO @StudentCourses VALUES (121311, GetDate(), 'Science')
INSERT INTO @StudentCourses VALUES (211131, GetDate(), 'Reading')
SELECT
ID,
CourseDate,
CASE
WHEN TargetValue IS NULL THEN CourseName
ELSE TargetValue
END AS NewCourseName
FROM @StudentCourses AS Courses
LEFT JOIN @Mapping AS Map
ON Courses.CourseName LIKE '%' + Map.SourceValue + '%'