SQL Full-Text Search Engine Query of Words in Sentences?

Q. How does a search engine look-up all the individual words within a sentence type into a typical web site textbox?

One word searches are easy using the LIKE statement.

There must be a better code than the one below where a use must enter a single word into each textbox....

Select ArticleTitle, ArticleDescription, ArticleKeywords, ArticleUrl
from Article
where ArticleTitle like '% ' " + searchTextbox1.Text.ToString + " ' %'  
OR ArticleTitle like '% ' " + searchTextbox2.Text.ToString + " ' %'  
OR ArticleTitle like '% ' " + searchTextbox3.Text.ToString + " ' %'  
OR ArticleDescription like '% ' " + searchTextbox1.Text.ToString + " ' %'
OR ArticleDescription like '% ' " + searchTextbox2.Text.ToString + " ' %'
OR ArticleDescription like  '% ' " + searchTextbox3.Text.ToString + " ' %'
OR ArticleKeywords like  '% ' " + searchTextbox1.Text.ToString + " ' %'
OR ArticleKeywords like '% ' " + searchTextbox2.Text.ToString + " ' %'
OR ArticleKeywords like '% ' " + searchTextbox3.Text.ToString + " ' %'
LVL 1
kvnsdrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thetoolCommented:
One thing you could do would be to split the text entered at the spaces.  So in this case:

string[] searchFor = searchTextBox1.Text.ToString().Split(' ');
string SQL="Select ArticleTitle, ... from Article where ";
for (int i=0;i<searchFor.Count;i++)
{
   if(i!=0)
   {
      SQL=SQL+"OR ";
   }
   SQL=SQL+"ArticleTitle like '%"+searchFor[i]+"%' or ArticleDescription like '%"+searchFor[i]+"%' or ArticleKeywords like '%"+searchFor[i]+"%'";
}

That should get you going.
Bob LearnedCommented:
Good reference:

Building Search Applications for the Web Using Microsoft SQL Server 2000 Full-Text Search
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_fulltextsearch.asp

Using CONTAINS and FREETEXT queries.

Bob
kvnsdrAuthor Commented:
Please excuse my sillyness, I'm new and attemping to try and understand.

1. Thank you for the code example. I would like to read the results to Windows Forms listBox1.
2. Thank TheLearnedOne for the msdn link. I choose not to use the actual MS Indexing Service due to it's maximun asp Internet user limits of 8- 16.

Here's my feable attempt at improvising your code to read the results of the search into a listBox1. I will eventually read the results into a datagrid. For now the listBox1 is simpler.....

Also received a compile error on searchFor.Count. I replaced it with Length.........

private void btnSearch_Click(object sender, System.EventArgs e)
{
try
{
string SQL="Select * FROM Article";

SqlConnection cn1 = new SqlConnection(ConfigurationSettings.AppSettings["AppSqlCon"]);
SqlDataAdapter da1 = new SqlDataAdapter(SQL1,cn1);
cn1.Open();
SqlDataReader reader1 = da1.SelectCommand.ExecuteReader();

string[] searchFor = textBox1.Text.ToString().Split(' ');

for (int i=0; i<searchFor.Length; i++)
{
if(i!=0)
{
SQL = SQL +"OR ";
}
SQL= SQL +"ArticleTitle like '%"+searchFor[i]+"%' or ArticleDescription like '%"+searchFor[i]+"%' or ArticleKeywords like '%"+searchFor[i]+"%'";
listBox1.Items.Add(searchFor);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
cn1.Close();
}
}
}
}
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

kvnsdrAuthor Commented:
Corrections:

SQL = SQL + "WHERE.............

listBox1.Items.Add(searchFor[i].ToString());
}
cn1.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
thetoolCommented:
To make sure I am correct you are trying to put the results of the search in the listbox not the keywords.  To do that you would actually have to execute the SQL variable I give you, store the results in a dataset, and then display the dataset in the listbox.  

Something like:

DataSet dstData=new DataSet();
for (int i=0; i<searchFor.Length; i++)
{
if(i!=0)
{
SQL = SQL +"OR ";
}
SQL= SQL +"ArticleTitle like '%"+searchFor[i]+"%' or ArticleDescription like '%"+searchFor[i]+"%' or ArticleKeywords like '%"+searchFor[i]+"%'";
SqlCommand cmd= new SqlCommand(SQL,cn1);
SqlDataAdapter adp = new SqlDataAdapter();
adp.SelectCommand=cmd;
adp.Fill(dstData,"results");
foreach(DataRow dataRow in dstData.Table["results"].Rows)
{
listBox1.Items.Add(dataRow["showMe"].ToString());  //showMe is a result column
}
dstData.Tables["results"].Clear();
}

That should work, maybe some minor syntax issues.  Use it to replace your for statement.
kvnsdrAuthor Commented:
Q. What is the "if" statement saying?

It looks like an OR whether the textbox is emty or full.
thetoolCommented:
The if statement is checking if it is NOT your first iteration through the loop.  If it wasn't there your SQL would end up something like

select * from table where or ArticleDescription like '%search%';

So it is just preventing there from being an or statement after where and before your first condition.
thetoolCommented:
I made an error the last example I gave you.  The SqlCommand, SqlDataAdapter, and foreach loop should be after of the original for loop.  A corrected version along with commenting on what almost every line does is below.

string[] searchFor = textBox1.Text.ToString().Split(' ');  //Gets an array of the individual words entered into the textbox
DataSet dstData=new DataSet();  //Declares dataset to store results
for (int i=0; i<searchFor.Length; i++)  //Loops through the array of individual words from the textbox
{
if(i!=0)  //Checks if NOT first iteration through loop
{
SQL = SQL +"OR ";  //Adds or to SQL if NOT first iteration through loop meaning there is already a condition added to the SQL
}
SQL= SQL +"ArticleTitle like '%"+searchFor[i]+"%' or ArticleDescription like '%"+searchFor[i]+"%' or ArticleKeywords like '%"+searchFor[i]+"%'";  
  //^adds conditions to SQL using a single word from the textbox stored in the array
}

SqlCommand cmd= new SqlCommand(SQL,cn1);
SqlDataAdapter adp = new SqlDataAdapter();
adp.SelectCommand=cmd;
adp.Fill(dstData,"results");  //Gets SQL results
foreach(DataRow dataRow in dstData.Table["results"].Rows)  //Loops through SQL results so they can be displayed
{
listBox1.Items.Add(dataRow["showMe"].ToString());  //showMe is a result column  //Displays results from SQL statement
}
dstData.Tables["results"].Clear();  //
Clears the results from the table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.