T-SQL Using CONTAINS predicate with Hyphen

Imagine a table (table1) with one column (column1) and one record whose value is 'roll-over'. Then use the following SQL query and you will not get any recor开发者_JAVA百科ds.

select * from table1 where contains(column1, ' "roll-over" ')

Is there a way to escape the hyphen in the search text? So far I have not been successful trying this (I have tried all below escapes with no success).

select * from table1 where contains(column1, ' "roll\-over" ')
select * from table1 where contains(column1, ' "roll!-over" ')
select * from table1 where contains(column1, ' "roll[-]over" ')

Also, please note that using the LIKE keyword is not possible for my application because I am taking advantage of full-text search indexing.

---

It looks like you may not be able to do that. Give this article a read:

https://support.microsoft.com/en-us/help/200043/prb-dashes---ignored-in-search-with-sql-full-text-and-msidxs-queries

They suggest searching only alphanumeric values (lame) or using the LIKE Clause (not an option for you).

---

Partial solution: you can force the query to return records containing hyphens (or any character) by using the charindex function to test that the string contains the character, e.g.:

select * from table1 where contains(column1, ' "roll-over" ')
and charindex('-', column1) > 0