Solved

Using FREETEXT SEARCH to do Partial Match

Posted on 2007-11-14
8
819 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server how to use a VARIABLE to link tables in a SQL Script? 3 41
mssql 7 32
database audit for object access 6 42
What does "Between" mean? 6 48
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

752 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