Help with fulltext search

Hello I have set up fulltext searching on my productsearch table on the following columns:
Contributor, PrimItemNumber, SecItemNumber, Title

The promitemnumber and secitemnumber are ISBNs

I want to allow search on any of those columns and created a statement:

select productid, title
from productsearch
where freetext((Contributor, PrimItemNumber, SecItemNumber, Title), 'Bernadette DiFrancesco')

This brings back products where the contributor could have any combination of first and last names which contains the two words passed in.

I need it to narrow the results as more information is given.

if I use either of the isbn the exact product is returned. gTg

If a product title is passed in for example 'Gina Ds Cuddle Bug Christmas' then I get thousands of products where each word may be in the title.

Once again I must narrow the result set as more information is given.

How is this accomplished???
gogetsomeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

virtuadeptCommented:
The way you can do this is to break up the search term list into a list of search terms:


select productid, title
from productsearch
where freetext((Contributor, PrimItemNumber, SecItemNumber, Title), @term1)
and freetext((Contributor, PrimItemNumber, SecItemNumber, Title), @term2)
and freetext((Contributor, PrimItemNumber, SecItemNumber, Title), @term3)


That probably is not very efficent.

Another way to do it is to break Title out of your other query, and build a string of the title with " AND " between each word.

Passed variable = 'This Is A Book Title'
New search variable = 'This AND Is AND A AND Book AND Title'

Then do:

select productid, title
from productsearch
where freetext((Contributor, PrimItemNumber, SecItemNumber), @passed_variable)
OR CONTAINS(Title, @new_search_variable)

FREETEXT does not allow boolean operators but CONTAINS does.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gogetsomeAuthor Commented:
I like the second approach, but what is the syntax that I need to use to separate the string and insert the AND conditions?

Do you know of an available function that I can call on a string that would create that syntax?
0
gogetsomeAuthor Commented:
Yes, I get it.. I have to use contains with Boolean operators and can either spit my string and reconstruct before passing to SQL.

Still wish I had a SQL function for that...
0
virtuadeptCommented:
Yes, lets say you have :

@passed_variable = 'This Is A Book Title'

And you want AND between each word,

--replaces spaces with space-AND-space
SET @passed_variable = REPLACE(@passed_variable,' ',' AND ')


REplace will work on columns too if you're using a column not a variable.
0
virtuadeptCommented:
Here is a better code block:

@arg_string = 'This Is A Book Title'
@anded_arg_string = REPLACE(@arg_string,' ',' AND ')

select productid, title
from productsearch
where freetext((Contributor, PrimItemNumber, SecItemNumber), @arg_string)
OR CONTAINS(Title, @anded_arg_string)

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.