1

I want to have the following query:

SELECT COUNT(*) FROM MyTable WHERE
CONTAINS (MyField, '(429)')

The problem, is that the parenthesis are ignored and it is finding anything with "429". I just want "(429)" found. If I change it to have '"(429)"' that does not help (it still returns items with just "429").

Kirk Liemohn
  • 7,733
  • 9
  • 46
  • 57
  • Parentheses are used to change the order of evaluation of logical operators. Even though you are not using logical, SQL Server is interpreting "(429)" as a parenthesized group and thus only using the "429" – KiwiPiet Nov 10 '15 at 18:52
  • So is it also finding '(429)'. If so I suspect FULLTEXT is parsing out the 429 and ( ) are just separators that are thrown out. – paparazzo Nov 10 '15 at 19:16
  • It seems there is a similar question ([link](http://stackoverflow.com/q/995478/2523686)). Also, check this ([link](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1d27b9eb-9921-4d80-bd43-4b318964dc22/fulltext-search-with-symbols-?forum=transactsql)) – i-one Nov 11 '15 at 07:42
  • Maybe it's a noise word? – user2404597 Nov 10 '15 at 17:33
  • Good idea, but if it is a noise word, wouldn't it return no results? I am getting too many results. – Kirk Liemohn Nov 10 '15 at 17:51

1 Answers1

0

Maybe you should explicitly convert (429) to a varchar.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a16434f3-5b94-44a1-a8cf-d66f832e1195/convert-integer-to-string

Lane Goolsby
  • 594
  • 1
  • 8
  • 26
  • Thanks @El Hombre (yes, I know who you are). I'll have to give that a shot. – Kirk Liemohn Nov 11 '15 at 17:25
  • (replace _at_ with an at sign below - silly stack overflow) I'm trying this but getting zero results. Very odd. I'm just doing DECLARE _at_MyVar VARCHAR followed by SET _at_MyVar = '(429)' and then replacing the second parameter of the CONTAINS function in my query to _at_MyVar and am getting zero results. I have tried using NVARCHAR, setting the variable to N'(429)' or '429' or N'429' with zero results as well. I must be missing something simple. – Kirk Liemohn Dec 12 '15 at 15:43