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
@CAT_ID int = 0,
@OrderBy int = '',
@PageIndex int = 1,
@PageSize int = 20
DECLARE @StartRow int
DECLARE @EndRow int
SET @StartRow = (@PageSize * (@PageIndex - 1)) + 1
SET @EndRow = @PageSize * @PageIndex + 1
IF (@CAT_ID = 0)
SET @CAT_ID = Null
SET NOCOUNT ON;
WITH RecipeSearch AS
-- Dynamic sorting
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,
CAST(R1.Ingredients as nvarchar(max)) as Ingredients,
CAST(R1.Instructions as nvarchar(max)) as Instructions,
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)