Solved

Full-Text search multiple keywords

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

710 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