Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Full-Text search multiple keywords

Posted on 2013-02-05
4
Medium Priority
?
509 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

688 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