Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Full-Text search for string with minus sign

Status
Not open for further replies.

Kliot

Programmer
Jan 10, 2003
622
US
I have a table set up with full-text searching, the problem I'm having is when I try to search a string with a minus sign like "A-Z of flowers." If I search for A-Z I come up with an empty set, how do I set it so the "-" is treated as part of the string?

SELECT * FROM Inventory.Inventory WHERE CONTAINS(Description, 'A-Z')

Thanks
Perrin
 
Just guessing...


SELECT * FROM Inventory.Inventory WHERE CONTAINS(Description, '[!]"[/!]A-Z[!]"[/!]')

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
I also thought that might work but it didn't.
 
have you tried:

SELECT * FROM Inventory.Inventory WHERE CONTAINS(Description, '[A-Z]')

Don't have any full text indices so i couldn't check.

-Sometimes the answer to your question is the hack that works
 
Yup, doesn't work either. I think I found the answer and it's "You Can't."

Here is what I found

SYMPTOMS
When performing a full-text search on SQL Server character data, or when using a SQL distributed query with the Microsoft Index Server OLE DB provider (MSDIXS) and a prefix expansion search for a compound word that contains a hyphen (for example, "XYZ-A*"), the results produced may not be as expected.
Back to the top

CAUSE
A full-text search considers a word to be a string of characters without spaces or punctuation. The occurrence of a non-alphanumeric character can "break" a word during a search. Because the SQL Server full-text search is a word-based engine, punctuation generally is not considered and is ignored when searching the index. Therefore, a CONTAINS clause like 'CONTAINS(testing, "computer failure")' would match a row with the value, "The failure to find my computer would be expensive.".


Back to the top

WORKAROUND
To work around this problem, try the following: • Only use alphanumeric characters when using the SQL Server full-text index facilities.

-or-
• Where non-alphanumeric character must be used in the search critera (primarily the dash '-' character), use the Transact-SQL LIKE clause instead of the FULLTEXT or CONTAINS predicates.

 
I may have mis-interpreted your request.
Were you trying to search for all the chars from A to Z or were you trying to search for a pattern of Alpha dash alpha?

-Sometimes the answer to your question is the hack that works
 
Alpha dash Alpha, as in "A-Z of flowers"
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top