Click here to Skip to main content
15,668,517 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I need to get results from a datatable.Select where this column contains long string.
User searches with multiple terms at a time.

For Example

If user is searching as Injection Spine ::

I am having 10,000 rows in a column.

Now i need to show rows where it matches both Injection and Spine. I do not know where these 2 terms will be in that string.

So, i need to use Like Condition. I am not sure how i can use it. Following are eight sample lines of data out of user should get only first line and second lines only while other all lines contains string Injection, but not Spine.

Injection/Aspiration of Spine, Diagnostic/Therapeutic 
Injection procedure, arterial, for occlusion of arteriovenous malformation, spine
Injection, intralesional; up to and including 7 lesions
Injection, intralesional; more than 7 lesions
Subcutaneous injection of filling material (eg, collagen); 1 cc or less
Subcutaneous injection of filling material (eg, collagen); 1.1 to 5.0 cc
Subcutaneous injection of filling material (eg, collagen); 5.1 to 10.0 cc
Subcutaneous injection of filling material (eg, collagen); over 10.0 cc
Injection(s); single tendon origin/insertion

Thanks In advance.

What I have tried:

DataRow[] ProRow = MainDS.Tables["Table1"].Select("LDesc Like '%" + SearchTerm + "%' And Active = 1 and Type in('CPT')");
Updated 27-Sep-16 5:05am
[no name] 27-Sep-16 8:40am    
How is it that you have determined that Spine is the same as spinal? You need to define your business rules.
Charles Shob 27-Sep-16 8:49am    
It is a typo. Not an issue with business rules. Corrected the question.
Sinisa Hajnal 27-Sep-16 9:24am    
insert % between any two words in searchterm that is not surrounded by quotes. Thus, for spine injection you would get %spine%injection% and you need the reverse too

If they are grouped together by quotes "spine injection" then you seek that whole term.

You could also look at NEAR clause in SQL server and see if that helps
Charles Shob 27-Sep-16 10:18am    
Thanks brother for quick response. But, i need this in DataTable.Select in C# not in SQL Server. How ever, i have tried the following code in c#. but getting an error providing for your review.

string filter = string.Empty;
for (int i = 0; i < temp.Length; i++)
filter = filter + "%" + temp[i].ToString().Trim();
filter = filter + "%";
DataRow[] row = MainDS.Tables["Table1"].Select("LDesc like "+ filter +" And Active = 1 and Type in('CPT')");

Thanks in Advance.

1 solution

You can use Linq To DataSet[^]:

DataTable dt = MainDS.Tables["Table1"]
var result = dt.AsEnumerable()
    .Where(x=>x.Field<string>("FieldName").Contains("Injection") && 

Above query should return a list of DataRows which meet criteria.

For further details, please see:
LINQ to DataSet Examples[^]
Queries in LINQ to DataSet[^]
Querying DataSets (LINQ to DataSet)[^]

If you can't use Linq, you can filter data on database level. To be able to do that, you should use SqlCeCommand[^] together with parameters[^]. To get results, use ExecuteReader()[^] and read data into SqlCeDataReader[^]. Finally, use DataTable.Load()[^] method to get data from SqlCeDataReader.

Good luck!
Share this answer
Charles Shob 27-Sep-16 21:27pm    
Thanks for the response Maciej Los. But, project is on .net 2.0 so, i caanot use LINQ for this. Hence, i am struggling.
Maciej Los 28-Sep-16 2:54am    
Another way to achieve that is to query your database then reload datatable. What database do you use?
Charles Shob 29-Sep-16 11:24am    
I am using SQL CE 4.0
Maciej Los 29-Sep-16 14:45pm    
See updated answer.
Charles Shob 30-Sep-16 23:23pm    
Thanks for the reply. WIll try and get back with results.

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900