Search for a column with comma separated values

Question

I have the version of SQL Server 2008 r2 that I am working on and the .Net webpage as an interface.

In this regard, the database has a column, which mainly consists of various skill sets, separated by a comma. For example, a candidate has 3 different skill sets, namely C# , SQL server , Oracle . A user who wants to list candidates with both C# and Oracle skills will provide input of both C#, Oracle to a text box on a web page. I want to write a query that can list such. I tried searching freetext. But he cannot get it if in Capital / Little Words there is no support for a wildcard character, not even an order of skills.

Below is an example query

 Select * from profiles where freetext(skills, 'C#,Oracle') 

Answer 1

From my POV, the correct (and undesirable) answer is to reverse engineer the structure of your table: there should never be a list of values ​​in one field IF YOU WANT TO ACCESS THEM RELATIVELY.

Answer 2

DISCLAIMER . I agree with @EugenRieck (+1 to this answer) - stuffing a CSV string in one field is a bad design.

But if you must ... look here at the beginning . Or try the CLR solution .

Answer 3

This one is good for full text.

Match Both C # and Oracle

 select * From Profiles where contains(*,'"Oracle" and "C#"') 

Matches Either C # or Oracle

 select * From Profiles where contains(*,'"Oracle" or "C#"')