Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.
Course Of The Month
9 days, 20 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Full Text search containg hyphen "-"
Posted on 2004-08-28
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?
[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-
3
10 Comments
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.
Also, please indicate the language you are using. In other words, I am interested to find out the noise.* file used.
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.
>>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.
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.
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.
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.
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.
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.
Featured Post
For Any Use & On Any Platform
Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 20 hours left to enroll
624 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.