Full-Text search for string with minus sign

[Kliot]

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

 

[gmmastros]

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

 

[Kliot]

I also thought that might work but it didn't.

 

[Qik3Coder]

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

 

[Kliot]

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.

 

[Qik3Coder]

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

 

[Kliot]

Alpha dash Alpha, as in "A-Z of flowers"