Solved

Full-Text search multiple keywords

Posted on 2013-02-05
4
493 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 500 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
 

Author Closing Comment

by:jay-are
ID: 38875434
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now