Solved

Full Text Search Single Digit Problem

Posted on 2011-03-02
12
1,061 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
  • 5
  • 4
  • 3
12 Comments
 
LVL 39

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 4

Expert Comment

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

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 39

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 39

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 39

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…

830 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