• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 519
  • Last Modified:

Full-Text search multiple keywords

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
jay-are
Asked:
jay-are
  • 3
2 Solutions
 
jay-areAuthor Commented:
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
 
Christopher GordonSenior Developer AnalystCommented:
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
 
jay-areAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now