sql server - Joining a table based on comma separated values

Question

How can I join two tables, where one of the tables has multiple comma separated values in one column that reference an id in another column?

1st table

Name    | Course Id
====================
Zishan  | 1,2,3                                           
Ellen   | 2,3,4                

2nd table

course id | course name 
=======================
   1      |  java
   2      |  C++
   3      |  oracle
   4      |  dot net

Answer 1

Maybe this uglyness, I have not checked results:

select names.name, courses.course_name
from names inner join courses
    on ',' + names.course_ids + ',' like '%,' + cast(courses.course_id as nvarchar(20)) + ',%'