[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Full-Text search multiple keywords

Posted on 2013-02-05
4
Medium Priority
?
517 Views
Last Modified: 2013-02-11
Hello Experts:

I've setup a simple search app for my website, but I'm having trouble getting multiple keyword search to work.  I added a full-text index to the table I'm searching.  I search keywords on four different columns in my table, so I'd like to search multiple keywords on each of those columns.  Here is the select statement I use to populate my gridview:

jobpost.SelectCommand = "select [jobid], [jobtitle], [companyname], [salaryrange1], [salaryrange2], [commissionamount], " & _
            "[jobcity], [jobstate], [jobindustry], [jobstatus], [jobpostdate], [specialties] FROM [jobpost] " & _
                "where CONTAINS((jobtitle, companyname, jobcity, jobstate), '" & strtbSearch & "') and (jobindustry like '" + ddlJobIndustry.SelectedValue + "'" & _
            " and specialties like '" + ddlSpecialties.SelectedValue + "' )"

Open in new window


Here is the definition of strtbSearch:

If tbKeywords.Text Is Nothing Then
            strtbSearch = """"
        Else
            strtbSearch = tbKeywords.Text.Replace(" ", " AND ")
        End If

Open in new window


I'm not sure how to get it to return all rows if the user leaves the textbox blank.  I'm also not sure how to get multiple keywords working.  I tried replacing spaces with " AND ", but this syntax doesn't work, it returns nothing.  

How do I use CONTAINS() to search multiple columns on multiple keywords?
0
Comment
Question by:jay-are
  • 3
4 Comments
 

Author Comment

by:jay-are
ID: 38856745
Solved the empty textbox search by changing the If/Then to:

If tbKeywords.Text = Nothing Then
            strtbSearch = "''"
        Else
            strtbSearch = tbKeywords.Text.Replace(" ", " OR ")
        End If

Open in new window



Still unable to figure out how to search multiple keywords across multiple columns.
0
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 2000 total points
ID: 38856747
I haven't had to do too much with Full Text, but this is the code I used to get the text box blank scenario to work.  I'm guess it would work similar with contains as well but I haven't tested it.  Note the first part of the compound where clause is double quotes surrounded by single quotes.

declare @SearchString nvarchar(4000) = 'red, yellow, blue, white'

select *
from yourTable

WHERE (( @SearchString = '""') OR freetext(YourColumn, @SearchString))
0
 

Accepted Solution

by:
jay-are earned 0 total points
ID: 38860196
As usual I'm horrible at asking the right questions, and don't get a lot of responses.  I managed to figure out what the issue is.  Obviously I can't search the same column for keyword AND keyword.  

The solution is to create a computed column for the fulltext index.  I created a new column and added the four columns to that one column.

Now I can search on this new computer column for keyword AND keyword and it will return the desired rows.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question