Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Force exact match in sql server freetext

Posted on 2010-08-31
7
Medium Priority
?
1,999 Views
Last Modified: 2012-05-10
I have a freetext SP that performs a search against a fairly large varchar column containing a large block of text.

I want to do a search

against a column something like:
almond mayonnaise "orange peel"
-------
Almond-Crusted Catalan Chicken  1 cut-up broiler-fryer chicken, skinned 1/4 cup mayonnaise 3 garlic cloves, finely minced 2 tablespoons fresh orange juice 2 teaspoons freshly grated orange peel 2 teaspoons honey 1/4 teaspoon cinnamon, divided 3/4 teaspoon salt, divided 1/2 teaspoon pepper, divided 1 cup fine dry plain breadcrumbs 1/2 cut sliced almonds 3 tablespoons olive oil  In large mixing bowl, make marinade by mixing together mayonnaise, garlic, orange juice, orange peel, honey, 1/8 teaspoon of the cinnamon, 1/4 teaspoon of the salt and 1/4 teaspoon of the pepper.  Add chicken, turning to coat.  Cover and refrigerate 1 hour, occasionally spooning marinade over chicken.  In pie pan, mix together remaining 1/2 teaspoon cinnamon, breadcrumbs, almonds and olive oil.  Bake, stirring occasionally, in 350F. oven about 15 minutes or until golden brown.  Remove chicken from marinade and roll, one piece at a time, in breadcrumb mixture.  Place chicken in single layer in lightly greased shallow baking pan.  Bake in 350F. oven about 1 hour or until crisp and brown and fork can be inserted in chicken with ease.  Makes 4 servings. Family Meals Dinner Bake/Roast Chickenpart Whole Marinade Southern
----

where "orange peel" is only given ranking value if it is an exact match.

Is this possible?

Here is my existing SP







@Search varchar(50),
@CAT_ID int = 0,
@OrderBy int = '',
@PageIndex int = 1,
@PageSize int = 20

AS
BEGIN

DECLARE @StartRow int
DECLARE @EndRow int

SET @StartRow = (@PageSize * (@PageIndex - 1))  + 1  
SET @EndRow = @PageSize * @PageIndex + 1

IF (@CAT_ID = 0)
 BEGIN 
      SET @CAT_ID = Null
 END

SET NOCOUNT ON;

WITH RecipeSearch AS
(
    SELECT DISTINCT
       ROW_NUMBER() OVER 
         (
           -- Dynamic sorting
            ORDER BY 
                  CASE WHEN @OrderBy = 1 THEN HITS END DESC,
                  CASE WHEN @OrderBy = 2 THEN NO_RATES END DESC,
                  CASE WHEN @OrderBy = 3 THEN Name END,
                  CASE WHEN @OrderBy = 4 OR @OrderBy = '' THEN Date END DESC
         ) AS RowNumber, 
               R1.ID, 
R1.Name, 
R1.Author, 
CAST(R1.Ingredients as nvarchar(max)) as Ingredients, 
CAST(R1.Instructions as nvarchar(max)) as Instructions, 
R1.Date,
R1.HOMEPAGE, 
R1.LINK_APPROVED, 
R1.HITS, 
R1.RATING, 
R1.NO_RATES, 
R1.TOTAL_COMMENTS, 
R1.HIT_DATE, 
R1.RecipeImage,
R1.FullTextSearch,
R1.TimeText,
R1.TimeValue,
fulltextSearch.[RANK] as SearchRank,
Cast((1.0 * RATING/NO_RATES) as decimal(2,1)) as Rates,
              dbo.GetSearchResultCount(@Search, @CAT_ID) As RCount
       FROM        RECIPES R1 JOIN FreeTextTable(Recipes,FullTextSearch,@Search) fulltextSearch ON R1.[ID] = fulltextSearch.[KEY]
       WHERE LINK_APPROVED = 1  AND FREETEXT(FullTextSearch,@Search)

Open in new window

0
Comment
Question by:mcunn
[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
7 Comments
 
LVL 21

Expert Comment

by:alainbryden
ID: 33570665
To only include entire phrase matches try
WHERE FREETEXT(FullTextSearch,@Search) AND FullTextSearch like ('%' + @Search + '%')

0
 
LVL 40

Expert Comment

by:lcohan
ID: 33570732
FREETEXT considerations from SQL BOL:
[...]
Because "parameter sniffing" does not work across conversion, use nvarchar for better performance. In the example, declare @SearchWord as nvarchar(30).
[...]
Unlike in the CONTAINS search condition where AND is a keyword, when used in freetext_string the word 'and' is considered a noise word and will be discarded.
[...]
If freetext_string is enclosed in double quotation marks, a phrase match is instead performed; stemming and thesaurus are not performed.
[...]

So in my opinion you could do something like:


DECLARE @SearchWord nvarchar(200)
      SET @SearchWord = N'almond and mayonnaise and "orange peel"'
SELECT Description
FROM Product.ProductDescription
WHERE FREETEXT(Description, @SearchWord);
0
 

Author Comment

by:mcunn
ID: 33571034
Ok I think I answered my own qustion:
Icohan is apparently correct until a breaking change in sql server 2008:

----
Phrase searches are no longer allowed by FREETEXT and FREETEXTTABLE, which perform stemming and thesaurus matches regardless of whether single or double quotation marks enclose the search string. For example, searching on "fast ships" would return both "fast ships" and "ship your package fast".
----


alainbryden: You answer works for a single phrase only but would not perform any kind of fuzzy search.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:mcunn
ID: 33571083
Does anybody have a workaround for this:

Essentially I want to search for 'almond and mayonnaise and "orange peel"

A record containing only "almond" would be given some ranking
A record containing only "orange peel" would be given some ranking
A record containing only  "orange"  would not get a ranking

Thanks



0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33573661
>>Force exact match in sql server freetext<<
I guess I must be missing something, but why don't you use CONTAINS instead of FREETEXT?
0
 

Author Comment

by:mcunn
ID: 33579724
What I am after is a fuzzy search that contains any of the key words or phrases  or variations on the keywords in ranked order. As I understand it CONTAINS would pull only the records that had exact matches. Correct?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 33582662
>>understand it CONTAINS would pull only the records that had exact matches. Correct?<<
That is true (aside from the INFLECTIONAL and THESAURUS clauses), it is just that your title for this thread ("Force exact match in sql server freetext") seemed to indicate otherwise and the text in the question agreed with the title (''where "orange peel" is only given ranking value if it is an exact match).

In any case, now you got a dilema as FREETEXT does not support any type of boolean search as CONTAINS does.
So I would see if the following does it for you, if not you may be SOL:

WHERE CONTAINS(YourLargeVarcharColumn, '"almond" AND "mayonnaise" and "orange peel"')

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

598 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