Multiple like conditions on single column in datatable.select

Question

0.00/5 (No votes)

See more:

Hi,

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:

C#

DataRow[] ProRow = MainDS.Tables["Table1"].Select("LDesc Like '%" + SearchTerm + "%' And Active = 1 and Type in('CPT')");

Posted 27-Sep-16 2:20am

Charles Shob

Updated 27-Sep-16 5:05am

Maciej Los

v3

Add a Solution

Comments

[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
%injection%spine%

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 https://msdn.microsoft.com/en-us/library/ms142568.aspx 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

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)

Maciej Los · Answer 1 · 2016-09-27T05:05:00

Solution 1

You can use Linq To DataSet[^]:

C#

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

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)[^]

[EDIT]
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.

Try!
Good luck!

Posted 27-Sep-16 5:05am

Maciej Los

Updated 29-Sep-16 8:44am

v2

Comments

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.

Maciej Los 1-Oct-16 3:16am

OK.
Check this LINQBridge 1.3.0[^], to enable Linq solutions in your project ;)
Good luck!
Cheers
Maciej