T-SQL Using CONTAINS predicate with Hyphen

Question

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 records.

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.

possible duplicate of [SQL Server Fulltext search not finding my rows](http://stackoverflow.com/questions/5699556/sql-server-fulltext-search-not-finding-my-rows) — Aaron Bertrand, Aug 03 '11 at 17:58
There seems to be something really wrong with this. Not even this worked: select 1 as Id, 'hello "roll-over" world' as SomeText into test CREATE UNIQUE INDEX ui_SPID ON test(Id); CREATE FULLTEXT CATALOG ft AS DEFAULT; CREATE FULLTEXT INDEX ON test(SomeText) KEY INDEX ui_SPID WITH STOPLIST = SYSTEM; select * from test where contains(SomeText, 'hello') drop table test drop fulltext catalog ft — Milimetric, Aug 03 '11 at 18:02

score 4 · Accepted Answer · edited Oct 11 '17 at 20:57

4

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).

edited Oct 11 '17 at 20:57

David C

7,204
5
46
65

answered Aug 03 '11 at 17:50

Abe Miessler

82,532
99
305
486

score 1 · Answer 2 · answered Aug 06 '19 at 17:50

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

T-SQL Using CONTAINS predicate with Hyphen

2 Answers2