use filteration for multiple ID if they are in comma seperated (,)

Question

1.00/5 (1 vote)

See more:

, +

How to search a column seperated with (,).If FacilityId is exist in many column then it will return exact no of rows.

I have column Facility that contains multiple facilityId like 16,17,14,2,10.

For Example:

FacilityId 1:16,17,14,2,10
FacilityId 2:6,7,4,2,10
FacilityId 3:5,17,14,20,11
FacilityId 4:12,13,14,2,10
FacilityId 5:10,17,12,2,18

If i searched with Id 16 then table will return 1 no of rows
If i searched with Id 10 then table will return 4 no of rows
If i searched with Id 17 then table will return 3 no of rows
If i searched with Id 2 then table will return 4 no of rows
etc

Now I want to search facility with facilityId.

Posted 25-May-15 4:49am

itsathere

Updated 25-May-15 5:03am

v3

Add a Solution

Comments

Sergey Alexandrovich Kryukov 25-May-15 11:10am

In SQL or .NET code?
—SA

itsathere 25-May-15 11:14am

actually it'll be better if u explain it in linq otherwise whatever u want.
thanks

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-05-25T05:34:00

Solution 1

Seriously? Don't.
Convert the commas separated list into an array (or other collection) of integers and use that instead. Comma separated data is a pain to work with - and if it's coming form a database then change the DB design to add another table which cross references your faculty to your ids. It will make your life a lot simpler.

To change the comma separated data is relatively simple:

C#

int[] data = "16,17,14,2,10".Split(',').Select(s => int.Parse(s)).ToArray();

But you will have to do that each time you want to use it! It's a lot easier and neater (and generally quicker in the long run) to use a collection of integers to start with.
After that, to see if an individual collection contains a specific value is also trivial:

C#

bool doesItHaveOne = data.Contains(16);

But...if the data is in a separate table in the DB, it's even easier to do the whole task: SQL is designed for that kind of query!

Posted 25-May-15 5:34am

OriginalGriff

Comments

itsathere 25-May-15 11:49am

I have a list which contains many rows.So, please explain me to convert particular column of list to array

data.Contains(16) not working properly..it gives result of 6 and 16.

OriginalGriff 25-May-15 11:55am

If the code i showed above doesn't help, you will have to be more specific in what you are asking - I don;t understand from that what you are trying to achieve, or what part of it is causing you difficulties.

itsathere 25-May-15 12:06pm

var Physiciandetails = objDb.SelectAll().ToList();

Physiciandetails is a list where all table data is available. Now, i want a where clause like below to search FacilityId

var Physiciandetails = objDb.SelectAll().ToList().Where(a=>a.FacilityId==16);

but FacilityId column shows result is like 16,17,14,2,10

Is it possible to search FacilityId within where clause.If yes, then how will i convert particular column to array and will use the array to exact search

If it's not possible to search within list then explain the store procedure for similar result.

OriginalGriff 25-May-15 12:15pm

Change your DB.
Seriously. Storing "lists" of values as comma separated data is easy to set up, and easy to INSERT the data, but it's a PITA for everything else.
Instead, set up a second table which has an ID link to teh original, and a single value.
A row from the original table which was

Id 9765
List 16,17,14,2,10
Other data ...

Then becomes

Id 9765
Other data ....

And th2 second table gets
9764 42
9765 16
9765 17
9765 14
9765 2
9765 10
9766 23

You can then return the counts you want with a very simple JOIN and GROUP SQL query.
It's a lot easier to do that than you think, and it saves a lot of time, effort and development later.

itsathere 25-May-15 12:21pm

Good Idea..
thank u.