?
Solved

Full Text Search Single Digit Problem

Posted on 2011-03-02
12
Medium Priority
?
1,121 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 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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month3 days, 23 hours left to enroll

601 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