Solved

Full Text Search Single Digit Problem

Posted on 2011-03-02
12
1,070 Views
Last Modified: 2012-05-11
When the user enters 9 and there is an address starting with 9 it never returns a result, however searches for 19 does return records starting with 19

 
ALTER PROCEDURE [dbo].[FullTextSearch]
      @Phrase varchar(200)

AS
	  SET NOCOUNT ON
      DECLARE @All nvarchar(200)
      DECLARE @FirstPart nvarchar(100)
      DECLARE @SecondPart nvarchar(100)
      DECLARE @Cut int
      DECLARE @SinglePartSearch bit

      SET @SinglePartSearch = 1 
      SET @Cut = charindex('+', @Phrase)
      IF @Cut > 0 
	  BEGIN
            SET @SinglePartSearch = 0
            SET @FirstPart =  '"' + left(@Phrase,(@Cut - 1)) + '*" AND '
            SET @SecondPart = '"' + rtrim(substring(@Phrase,(@Cut+1), 100)) + '*"'
	  END

           
      CREATE TABLE #Results
      (
            RecordType int,
            RecordID int,
            Contact varchar(150),
            Address1 varchar(300),
            Address2 varchar(300),
            Address3 varchar(300),
            Address4 varchar(300),
            Address5 varchar(300),
            Address6 varchar(300),
            Tel1 varchar(50),
            Tel2 varchar(50),
            Tel3 varchar(50),
            Tel4 varchar(50),
            Tel5 varchar(50)
      )
      
      IF @SinglePartSearch = 1
            SET @All =  '"' + @Phrase + '*"'
      ELSE
            SET @All = @FirstPart + @SecondPart

      -- Applicant RecordType 1 
      INSERT INTO #Results 
      SELECT 1, Reference, ISNULL(Salutation, '') + ' ' + ISNULL(Forename, '') + ' ' + ISNULL(Surname, '') AS Contact, Address1, Address2, Address3, Address4, Address5, Address6, Phone1, Phone2, Phone3, Phone4,Phone5 FROM Applicant
      WHERE CONTAINS((Salutation, Forename, Surname, Address1, Address2, Address3, Address4, Address5, Address6, Phone1, Phone2, Phone3, Phone4, Phone5), @All)

Open in new window

0
Comment
Question by:rocky050371
[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
  • 5
  • 4
  • 3
12 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 35019167
Full text "...searches against text data in full-text indexes by operating on words and phrases based on rules of a particular language..." but "9" is no word or phrase so in my opinion you should read more about that at:

http://msdn.microsoft.com/en-us/library/ms142547.aspx

0
 
LVL 4

Expert Comment

by:MSSystems
ID: 35019173
I am thinking the problem should be in the filter. Ootherwise it could be a data type.

 
ALTER PROCEDURE [dbo].[FullTextSearch]
      @Phrase varchar(200)

AS
	  SET NOCOUNT ON
      DECLARE @All nvarchar(200)
      DECLARE @FirstPart nvarchar(100)
      DECLARE @SecondPart nvarchar(100)
      DECLARE @Cut int
      DECLARE @SinglePartSearch bit

      SET @SinglePartSearch = 1 
      SET @Cut = charindex('+', @Phrase)
      IF @Cut > 0 
	  BEGIN
            SET @SinglePartSearch = 0
            SET @FirstPart =  '"' + left(@Phrase,(@Cut - 1)) + '*" AND '
            SET @SecondPart = '"' + rtrim(substring(@Phrase,(@Cut+1), 100)) + '*"'
	  END

           
      CREATE TABLE #Results
      (
            RecordType int,
            RecordID int,
            Contact varchar(150),
            Address1 varchar(300),
            Address2 varchar(300),
            Address3 varchar(300),
            Address4 varchar(300),
            Address5 varchar(300),
            Address6 varchar(300),
            Tel1 varchar(50),
            Tel2 varchar(50),
            Tel3 varchar(50),
            Tel4 varchar(50),
            Tel5 varchar(50)
      )
      
      IF @SinglePartSearch = 1
            SET @All =  '"' + @Phrase + '*"'
      ELSE
            SET @All = @FirstPart + @SecondPart

      -- Applicant RecordType 1 
      INSERT INTO #Results 
      SELECT 1, Reference, ISNULL(Salutation, '') + ' ' + ISNULL(Forename, '') + ' ' + ISNULL(Surname, '') AS Contact, Address1, Address2, Address3, Address4, Address5, Address6, Phone1, Phone2, Phone3, Phone4,Phone5 FROM Applicant
      WHERE CONTAINS(Salutation, Forename, Surname, Address1, Address2, Address3, Address4, Address5, Address6, Phone1, Phone2, Phone3, Phone4, Phone5 or @All)

Open in new window

0
 

Author Comment

by:rocky050371
ID: 35019209
If that is the case then why would 19 18 etc work fine.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 4

Expert Comment

by:MSSystems
ID: 35019256
Can you send the table schema please? Sp_help [Applicant]
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35019337
Because they are not a single character. try search a or b or c - as far as I know nothing would be returned - same thing. Again, full text search is a "word" or "phrase" search engine not single number/character search.
0
 

Author Comment

by:rocky050371
ID: 35019390
a.b.c work fine
0
 
LVL 4

Expert Comment

by:MSSystems
ID: 35019440
Maybe lcohan is right. But like you pointed out with the a, b, c it worked. To completely eliminate it, try the following.

Sorely if you converted the string to more than one character it will be a word/phrase

ALTER PROCEDURE [dbo].[FullTextSearch]
      @Phrase varchar(200)

AS
	  SET NOCOUNT ON
      DECLARE @All nvarchar(200)
      DECLARE @FirstPart nvarchar(100)
      DECLARE @SecondPart nvarchar(100)
      DECLARE @Cut int
      DECLARE @SinglePartSearch bit

      SET @SinglePartSearch = 1 
      SET @Cut = charindex('+', @Phrase)
      IF @Cut > 0 
	  BEGIN
            SET @SinglePartSearch = 0
            SET @FirstPart =  '"' + left(@Phrase,(@Cut - 1)) + '*" AND '
            SET @SecondPart = '"' + rtrim(substring(@Phrase,(@Cut+1), 100)) + '*"'
	  END

           
      CREATE TABLE #Results
      (
            RecordType int,
            RecordID int,
            Contact varchar(150),
            Address1 varchar(300),
            Address2 varchar(300),
            Address3 varchar(300),
            Address4 varchar(300),
            Address5 varchar(300),
            Address6 varchar(300),
            Tel1 varchar(50),
            Tel2 varchar(50),
            Tel3 varchar(50),
            Tel4 varchar(50),
            Tel5 varchar(50)
      )
      
      IF @SinglePartSearch = 1
            SET @All =  '"' + CHAR(32) + @Phrase + '*"'
      ELSE
            SET @All = @FirstPart + @SecondPart

      -- Applicant RecordType 1 
      INSERT INTO #Results 
      SELECT 1, Reference, ISNULL(Salutation, '') + ' ' + ISNULL(Forename, '') + ' ' + ISNULL(Surname, '') AS Contact, Address1, Address2, Address3, Address4, Address5, Address6, Phone1, Phone2, Phone3, Phone4,Phone5 FROM Applicant
      WHERE CONTAINS(Salutation, Forename, Surname, Address1, Address2, Address3, Address4, Address5, Address6, Phone1, Phone2, Phone3, Phone4, Phone5 or @All)

Open in new window

0
 
LVL 40

Accepted Solution

by:
lcohan earned 500 total points
ID: 35019484
check the noise files see what's in there

http://msdn.microsoft.com/en-us/library/ms142551.aspx
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35019532
you can also check all stopwords for all languages by:

select * from sys.fulltext_system_stopwords

Please do note that "Full-text queries perform linguistic searches against text data in full-text indexes by operating on words and phrases based on rules of a particular language such as English "
0
 

Author Comment

by:rocky050371
ID: 35025698
The code with char(32) does not work, I cant see the number 9 being in a noise file. This is very strange
0
 

Author Comment

by:rocky050371
ID: 35026067
I checked the noise file again, it had the digits in them, it is SQL server 2005 by the way (realised I am in 2008 thread) I rebuilt the index but it is still exactly the same
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35028182
Did you removed the 9 from the noise file for the language you are testing on?

For SQL 2005 they should be located on your SQL server box at:
 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData

 If you did that, rebuilt your full text and is not working then I would take it with Microsoft.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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 2008 R2, need a pivot/cross tab query... 4 73
SQL server client app 3 71
SQL profiler 3 48
debug user defined function in visual studio 2015 2 44
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

738 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