How to use IN and LIKE both commands of sql server in one query for searching string

Question

2.33/5 (2 votes)

See more:

SQL-Server-2005

How to use IN and LIKE both commands of sql server in one query for searching string?Yes i checked.My question in that coding is.if i will put one sentence in one textbox that all the words will check one by one like google search.and it will display all related datas in datagridview.
Sir i want to put like this.but it is not correct.My coding is:

SQL

select *from CandidateDetails where like (% '"+textBox.Text+"'%) IN (firstname,lastname,middlename)

Posted 5-Apr-11 0:47am

Banajyotsna

Updated 5-Apr-11 1:38am

v4

Add a Solution

Comments

#realJSOP 5-Apr-11 9:22am

Repost

4 solutions

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)

Wild-Programmer · Answer 1 · 2011-04-05T00:49:00

Solution 1

For LIKE see this -

http://msdn.microsoft.com/en-us/library/ms179859.aspx[^]

and for IN see this -
http://msdn.microsoft.com/en-us/library/ms177682.aspx[^]

Posted 5-Apr-11 0:49am

Wild-Programmer

Comments

Banajyotsna 5-Apr-11 7:13am

Sir i am asking How to use LIKE and IN sql command in one query?

Mahendra.p25 · Answer 2 · 2011-04-05T00:54:00

Solution 2

try this

select * from CandidateDetails where FirstName in ('ra') or LastName like '%ul%'

Posted 5-Apr-11 0:54am

Mahendra.p25

Updated 5-Apr-11 1:36am

v2

Comments

Banajyotsna 5-Apr-11 7:16am

Sir in your example i want to put like this:- select * from CandidateDetails where FirstName in (like('%ra%')) .But it is not running.Can you rectify it ?

Mahendra.p25 5-Apr-11 7:43am

try this
select * from CandidateDetails where firstname+middlename+lastname like '%ul%'

Banajyotsna 5-Apr-11 9:16am

In the following coding how shall i use IN and Like in sqlcommand.My coding is:
private void btnSearch_Click(object sender, EventArgs e)
{
try
{
string commaDelimited = textBox1.Text;
string[] year = commaDelimited.Split(new char[] { });

/////////////////////////////////////////
//string[] r = { "TV", "Radio", "Coal" };
string filter = string.Empty;
foreach (string name in year)
{
if (filter.Length > 0)
{
filter += ",";
}
filter += string.Format("'{0}'", name);
}

SqlDataAdapter adp;
DataSet ds = new DataSet();
adp = new SqlDataAdapter("Select * from EMP_DET where FName IN (Like '%(" + filter + ")%')", con);
adp.Fill(ds, "EMP_DET");
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
catch
{ }

*dan!sh* · Answer 3 · 2011-04-05T00:56:00

Solution 3

Check this[^] thread. I feel you are looking for same thing.

Update: I assume that the there are column names after the in clause.

select * 
from CandidateDetails 
where (firstname like (% '"+textBox.Text+"'%)) OR
(lastname like (% '"+textBox.Text+"'%)) OR
(middlename like (% '"+textBox.Text+"'%)))

This may help.

Posted 5-Apr-11 0:56am

dan!sh

Updated 5-Apr-11 1:51am

v2

Comments

Banajyotsna 5-Apr-11 7:12am

Yes i am looking for same thing but this is not succeed .Try to solve my problem please

dan!sh 5-Apr-11 7:26am

Did you checked the link which explains the use of pipe operator in SQL? You can use that.

Banajyotsna 5-Apr-11 7:30am

Yes i checked.My question in that coding is.if i will put one sentence in one textbox that all the words will check one by one like google search.and it will display all related datas in datagridview.

dan!sh 5-Apr-11 7:47am

Updated the reply.

Banajyotsna 5-Apr-11 9:12am

Sir i used Like and IN sql commands in coding given by your previous link.I added IN and Like in sql command but it is not succeed.My coding is:
private void btnSearch_Click(object sender, EventArgs e)
{
try
{
string commaDelimited = textBox1.Text;
string[] year = commaDelimited.Split(new char[] { });

/////////////////////////////////////////
//string[] r = { "TV", "Radio", "Coal" };
string filter = string.Empty;
foreach (string name in year)
{
if (filter.Length > 0)
{
filter += ",";
}
filter += string.Format("'{0}'", name);
}

SqlDataAdapter adp;
DataSet ds = new DataSet();
adp = new SqlDataAdapter("Select * from EMP_DET where FName IN (Like '%(" + filter + ")%')", con);
adp.Fill(ds, "EMP_DET");
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
catch
{ }

Wendelius · Answer 4 · 2011-04-05T09:38:00

The easiest way to do this is to build the condition dynamically and use as many conditions (with OR's between) as you have words. So basically you would have a code like (in pseudo code):

sqltext = "start of the SQL  WHERE ("
loop through words
   SqlParameter p =  New SqlParameter()
   p.Name = "p" + counter
   if counter > 0 sqltext += " OR "
   sqltext += "Field LIKE @p" + counter
   p.Value = "%" + word + "%"
   SqlCOmmand.Parameters.Add(p)
   counter++
end loop
sqlcommand.commandtext = sqltext + ")"
exec

This could be done also otherwise, but it would still have a dynamic portion and it would require more knowledge on your table structure (like primary keys etc)

How to use IN and LIKE both commands of sql server in one query for searching string

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us