?
Solved

Full Text Search Single Digit Problem

Posted on 2011-03-02
12
Medium Priority
?
1,098 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

649 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