2

I have SQL server 2008 r2 edition, which am working on and a .Net webpage as front end.

Coming to the issue, there is a column in the database which basically consists of various skill sets separated by a comma. For example some candidate has a 3 different skill sets namely C#, SQL server, Oracle. The user who wants to list the candidates having the skills of both C# and Oracle will provide the input as C#, Oracle in a text box on the webpage. I want to write a query which can list out such. I have tried freetext search. But it fails to fetch if in Capital/small words, no support for wildcard character, even the order of skills.

Below is the sample query

Select * from profiles where freetext(skills, ‘C#,Oracle’)
BenMorel
  • 34,448
  • 50
  • 182
  • 322
santosh s
  • 41
  • 1
  • 6

3 Answers3

11

From my POV the correct (and unwelcome) answer is to redesign your table structure: You should never ever have a list of values in a single field, IF YOU WANT TO ACCESS THEM SEPARATELY.

Jon Egerton
  • 40,401
  • 11
  • 97
  • 129
Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
  • @JonEgerton: Please be the one representative of the SO community to take my thanks for all of those guys who bother to fix my (non-native) english language errors. Much appreciated, and I learn my most common mistakes – Eugen Rieck Dec 30 '11 at 14:50
  • Not a problem - your English is far better than my very basic German! – Jon Egerton Dec 30 '11 at 14:57
0

this is a good one about full-text.

Match Both C# and Oracle

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

Match Either C# or Oracle

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

Full-text: and/or

Rm558
  • 4,621
  • 3
  • 38
  • 43
0

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

But if you must...look here first. Or try a CLR solution.

kuujinbo
  • 9,272
  • 3
  • 44
  • 57