3

I'm running a TSQL query that uses a CONTAINSTABLE-statement like this one

CONTAINSTABLE(<Table A>, <TargetColumn>, '01100011')

which gives me the correct results. However, If I use

CONTAINSTABLE(<Table A>, <TargetColumn>, '0110001*')

instead, I get 0 result. Can someone please explain to my why ? AFAIK wildcards are supported like this.

This is on MSSQL Server 2008R2

Thanks in advance :-)

Dave
  • 81
  • 5
  • Could you please share the result of your first statement `'01100011'` A `*` acts as a `wildcard` and when you give the * after the `'01100011*'` its looks for the value `01100011` followed by any other character. – DataWrangler Aug 09 '17 at 10:00
  • The result are 2 rows from that have excaclty '01100011' in . I don't get why they are not found by the second statement, where the last 1 was replaced by * (which in my case should result in at lease the same result set).
     – Dave Aug 09 '17 at 11:27
  • 3
    Try `CONTAINSTABLE(.., .., '"0110001*"')` (mind the double quotes), per the [docs](https://learn.microsoft.com/en-us/sql/relational-databases/search/query-with-full-text-search#Prefix_Term). – Jeroen Mostert Aug 09 '17 at 11:53
  • @Dave please try as advised by Jeroen.. – DataWrangler Aug 09 '17 at 11:59
  • 1
    Works fine, thanks a lot. Is there a way of escaping ' or " within the term to search for ? I have use cases where both are beeing used within the search input, e.g people searching for terms like 2" diameter – Dave Aug 09 '17 at 13:57
  • @Dave: escaping will do you no good since full-text search [doesn't index punctuation](https://stackoverflow.com/q/2886011/). (One of the answer suggests disabling the stoplist, but I have no idea if that works.) In any case, that's a different question altogether. – Jeroen Mostert Aug 09 '17 at 14:50

1 Answers1

0

As per Jeroen's comment, you need to surround your search term with double quotes (within the single quotes).

The documentation gives the example CONTAINS (Description, '"top*"' ) then says

If the text and asterisk are not delimited by double quotation marks, as in CONTAINS (DESCRIPTION, 'top'), full-text search does not consider the asterisk to be a wildcard.

In your case, CONTAINSTABLE(<Table A>, <TargetColumn>, '"0110001*"') should work as you expected.

Tim
  • 5,435
  • 7
  • 42
  • 62