Alpha Video Training
Results 1 to 2 of 2

Thread: MS SQL Contains() Search Not Working

  1. #1
    Member
    Real Name
    Kyle Winters
    Join Date
    May 2010
    Location
    Phoenix AZ via Boston Ma
    Posts
    45

    Default MS SQL Contains() Search Not Working

    I have a SQL table where I have enabled full text indexing and I am using a contains predicate in a select statement. The contains() predicate will not search for a word that is only one letter long like N S E or W. This will not work through X-Basic or even directly through SQL Server Management Studio. See examples below:

    Code:
    SELECT parcel,propaddr,Sale_Date,DeedType,Xfile
      FROM [TimWEB].[dbo].[masternewnt]
    	WHERE contains(PropAddr,'"Glendale" and "Ave"')
    	order by PropAddr
    This will return all records with Glendale Ave as expected

    Code:
    SELECT parcel,propaddr,Sale_Date,DeedType,Xfile
      FROM [TimWEB].[dbo].[masternewnt]
    	WHERE contains(PropAddr,'"Glendale" and "W"')
    	order by PropAddr
    This will not return any records at all.

    Note: This does not work for ANY one letter word. I have also tried it with a wildcard operator to no avail. I am trying to stay away from a LIKE operator because it is too slow when querying 1.5M records.

    Any of you SQL brains know what the deal is?

  2. #2
    Member
    Real Name
    Kyle Winters
    Join Date
    May 2010
    Location
    Phoenix AZ via Boston Ma
    Posts
    45

    Default Re: MS SQL Contains() Search Not Working

    Problem solved:

    To fight against bloated searches, when the developers at MS created Full Text Indexing they created a list of stop words. These include all one character words and common words that do not aid in a search such as on, in, an, the, ... etc. You will notice similar behavior in the MySQL DB that runs this message board. So this is all well and good until you need to search for directionals like N S E W. In this case you must change the stop word list for your full text index to one that you modify from the system list or create from scratch.

    Resources:

    On Full Text Indexing

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

    On Stop Words

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

Similar Threads

  1. Search not working?
    By dpara01 in forum Forum Usage
    Replies: 3
    Last Post: 02-11-2009, 08:29 AM
  2. Quick Search Not working for Joined SQL Tables?
    By MBertrand in forum Application Server Version 9 - Web/Browser Applications
    Replies: 0
    Last Post: 01-13-2009, 12:57 PM
  3. Single Search Not Working
    By stealthey_99 in forum Application Server Version 9 - Web/Browser Applications
    Replies: 3
    Last Post: 12-19-2008, 07:06 PM
  4. Single Search Not Working
    By stealthey_99 in forum Application Server Version 9 - Web/Browser Applications
    Replies: 0
    Last Post: 12-15-2008, 11:39 AM
  5. SQL Like Search
    By Gregory R. Zilliox in forum Web Application Server v6
    Replies: 1
    Last Post: 09-23-2005, 01:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •