Solved

Full Text Search Single Digit Problem

Posted on 2011-03-02
12
1,054 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

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…
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…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now