searching from comma separated string

Question

searching from comma separated string

gaurav-404321

SSCommitted

Points: 1593
More actions
July 19, 2010 at 3:05 am

#235341

I have a table where Job Seeker's Preferred Job Locations are getting saved as:
JobSeekerID PreferredJobLocations
1 1,2,3
2 6,7
3 4,6,9
where preferred joblocations values (comma separated) are locationids picked from LocationMaster table.
Now when employer is searching for Candidates he will be selecting the joblocations from search parameter.
Suppose he has selected candidates having preferred job location as 6,9 then candidates with JobSeekerID 2 and 3 must reflect in result.
Can anybody pleae guide how to design the query to get these jobseekers according to selected locations by employer.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

As you table design does look like flat-file and not a proper table of realtional database, I can guide you first how to design the table in relational database...

Your table better to be defines as:

CREATE TABLE YourTabelName

(

JobSeekerID INT

,PreferredJobLocation INT

)

Then you can store relation between JobSeekerID and Location as following:

JobSeekerID PreferredJobLocation

1 1

1 2

1 3

2 6

2 7

3 4

3 6

3 9

Then, your query will look like :

SELECT JobSeekerID FROM YourTabelName WHERE PreferredJobLocation = 6 OR PreferredJobLocation = 9

And it will return you two rows you need!

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Mike01 SSChampion Points: 11395 More actions · Answer 2

This post is very similar to what you are asking and has some useful solutions

http://www.sqlservercentral.com/Forums/Topic950057-149-1.aspx

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/