Solved

Using FREETEXT SEARCH to do Partial Match

Posted on 2007-11-14
8
821 Views
Last Modified: 2010-08-05
Hi there I am using FREETEXTSEARCH, Here are the clauses in question.

AND
((@Search is null or FREETEXT(DescriptionSelConcept, @Search) )
or
(@Search is null or FREETEXT(LessonsLearnt, @Search) )
or
(@Search is null or FREETEXT(ProjectTitle, @Search) )
or
(@Search is null or FREETEXT(BriefDescriptionScope, @Search) )
or
(@Search is null or FREETEXT(JobNumber, @Search) ))

At the moment these clauses only do exact match for instance if im searching for "blackhorse" in the ProjectTitle I MUST type in Blackhorse for the result to appear, I would like to be able to type "black" as well for it to appear, does anyone know how I can modify this to do this?
0
Comment
Question by:MayoorPatel
[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
  • 3
  • 2
8 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 20280653
try to use CONTAINS instead of FREETEXT:
http://msdn2.microsoft.com/en-us/library/aa258227(SQL.80).aspx
----------------------------------------
Example:

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "choc*" ')
GO
0
 
LVL 1

Author Comment

by:MayoorPatel
ID: 20280975
Nope just tried this

AND
((@Search is null or CONTAINS(DescriptionSelConcept, @Search) )
or
(@Search is null or CONTAINS(LessonsLearnt, @Search) )
or
(@Search is null or CONTAINS(ProjectTitle, @Search) )
or
(@Search is null or CONTAINS(BriefDescriptionScope, @Search) )
or

and its still not returning the record on partial match
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 20281198
try rtrim(@search) + '*'
0
Technology Partners: 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!

 
LVL 1

Author Comment

by:MayoorPatel
ID: 20281226
AND
((@Search is null or CONTAINS(DescriptionSelConcept, rtrim(@search) + '*') )
or
(@Search is null or CONTAINS(LessonsLearnt, rtrim(@search) + '*') )
or
(@Search is null or CONTAINS(ProjectTitle, rtrim(@search) + '*') )
or
(@Search is null or CONTAINS(BriefDescriptionScope, rtrim(@search) + '*') )
or


gives a syntax error in the SQL


0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 20282204
try

--
AND
((@Search is null or CONTAINS(DescriptionSelConcept,'"'+@search+'*"') )
or
(@Search is null or CONTAINS(LessonsLearnt, '"'+@search+'*"') )
or
(@Search is null or CONTAINS(ProjectTitle, '"'+@search+'*"' )
or
(@Search is null or CONTAINS(BriefDescriptionScope,'"'+@search+'*"') )
or

----------
see working example:


USE AdventureWorks;
GO
declare @search varchar(50)
set @search=' Chain '
set @search='"'+@search+'*"'
SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, @search);
GO
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 20282377
You might find it a little more efficient, if you set the @Search before and use * instead of repeating all the columns in endless OR clauses, as in:

SET @Search = '"' + @Search + '*"'

...

AND
@Search Is Null Or CONTAINS(*, @Search)

0
 
LVL 1

Author Comment

by:MayoorPatel
ID: 20287777
aceperkins - Yes that would be correct IF I had wanted to search every column in the table as I only need to search 4 of them the way I have done it is correct.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20288746
No problem.  On a different note, please attend to all your abandoned questions:
1 10/19/2007 500 Converting a Dynamic versio... Open ASP
2 10/08/2007 500 Building A Search Query Open MS SQL Server ...
3 09/14/2007 500 How to Search 2 Colums in a... Open MS SQL Server
4 07/04/2007 500 UNION with DYNAMIC ORDER BY... Open Databases ...
5 07/04/2007 500 Filtering a Datalist Orderi... Open ASP.Net Programm...
6 06/25/2007 500 Overlapping Rendering of El... Open Firefox ...
7 06/22/2007 500 Object reference not set to... Open ASP.Net Programm... ...
8 04/04/2007 500 Group By Clause Problem wit... Open SQL Server 2005 ...
9 03/27/2007 500 How to Avoid Divide by Zero... Open MS SQL Server
10 03/26/2007 500 Calculated Column to calcul... Open SQL Server 2005 ...

Thanks.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

624 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