TSQL in SQL 2005: Query

Question

I have 2 columns in my table, called TaskSet and SkillsSelected.

The sample data as follow:

TaskSet                        | SkillsSelected
--------------------------------------------------
SK000001, SK000004, SK000002   | SK000001, SK000002, SK000003
SK000002                       | SK000002, SK000003, SK000004

As you can see it's using comma to separate the data. I want a query that will give me the record that is not from the TaskSet that is not exist in the SkillsSelected so in this case will return:

SK000003 
SK000003, SK000004

Answer 1

The best way to deal with comma separated lists in SQL Server is to create a UDF that returns a table type. See this link for details. MS documentation claims that a CLR UDF is faster, but here's an actual comparison of the two options in use.

Once that is in place, you can use:

SELECT t.*
  FROM TABLE t
 WHERE EXISTS(SELECT value 
                FROM dbo.split(t.taskset)
              INTERSECT
              SELECT value 
                FROM dbo.split(t.skillsselected))

Reference:

• EXCEPT and INTERSECT