Solved

SQL Full Text search containg hyphen "-"

Posted on 2004-08-28
10
1,508 Views
Last Modified: 2008-03-04
I am searching a database that contains descriptions of various millitary and commercial models.  I use full text indexing with the contains statement for almost all of these searches.  I need to be able to seach for planes like a B-17, F105, C-5, etc.  What I have learned is the FT search ignores the "-" hyphen.  It also then ignores the letter preceding the hyphen.  Which then leaves me searching for 17, 105, 5.  The first two return more results than I want, since lots of other items contain 17 or 105.  But the last is a problem since 5 by itself will also be ignored.  Is there a way to work around this?  Can I somehow force or trick the FT index into searching for the the full word including the hyphen?

0
Comment
Question by:stratton65
  • 5
  • 3
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11926249
Try it this way:

SELECT *
FROM Table1
WHERE CONTAINS(*, '"B-17"')
0
 

Author Comment

by:stratton65
ID: 11927650
Unless I'm doing something wrong, that yeilds the same result.  It only looks for the '17'

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11928037
I suspect there is a misuderstanding as to how Full-Text Search really works.  Post the row that contains "B-17" and I can give you more details.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11928045
Also, please indicate the language you are using.  In other words, I am interested to find out the noise.* file used.
0
 

Author Comment

by:stratton65
ID: 11931231
I'me working in English.  I have been able to work around it by using       FIELD LIKE "%B-17%"    instead of the CONTAINS statement.  The Full Text index will ignore the hyphen by design.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11931534
>>FIELD LIKE "%B-17%" <<
If performance is not an issue, that that should be fine.  Just remember that using LIKE will probably force a sequential search.

>>The Full Text index will ignore the hyphen by design.<<
I am sorry that is not the way it works.  Do yourself a favor
1. Add a row with B-17 in it.  
2. Do a full-population and allow it to complete
3. When it has finished, do a search using: WHERE CONTAINS(*, 'B-17')

What is happening is that you have something like "AB-17" and expect Full-Text search to find that row with "B-17".  That is not going to happen.
0
 

Author Comment

by:stratton65
ID: 11931659
This is the knowledge base article that deals with hyphens;

http://support.microsoft.com/default.aspx?scid=kb;en-us;200043

Unless I'm missing something, this is exactly how the hyphens behave in my environment.  

This is an example of the row;

B-17 G ‘Chow-hound’ -- This diecast Historically Accurate plane comes fully painted with pad-printing (no decals), and its own stand. Also included is Historical Information unique to this plane.

CONTAINS(*,'B-17')   will return this row as well as any row with a '17' in it.

DESCRIPTION LIKE '%B-17%'     returns only the rows I want.

There is a performance concern so I would really like to stick with Full Text if there is a way to make it work.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 11931985
I see what you are getting at.  Unfortunately, I cannot duplicate the problem :)

Here is what I did.  
1. Created a table:
CREATE TABLE Table1 (
      ID int IDENTITY(1, 1) NOT NULL,
      Description text NULL)

2. Created a unique index on ID.

3. Added one row with the value:
B-17 G ‘Chow-hound’ -- This diecast Historically Accurate plane comes fully painted with pad-printing (no decals), and its own stand. Also included is Historical Information unique to this plane.

4. Added (Defined and did a Full Population) a full-text search on the Description column

5. Created a query with:
select * from table1 where CONTAINS(*, 'b-17')
And it returned that row.

Thinking there might be a problem with Prefix searches (as the article suggested) tried changing to CONTAINS(*, '"b-1*"'), the row was still returned.  I even tried: CONTAINS(*, '"pad-print*"').  Same result.

So, I would suggest you try the same.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

831 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