1

How can I parameterize the following query? (By the way I'm using a full text indexed table, hence the CONTAINS())

SELECT * FROM Table WHERE CONTAINS(Column, 'cat OR dog') 
and ((column NOT LIKE '%[^ cat OR dog]%'));

This didn't work:

 DECLARE @term1 VARCHAR(10);

 DECLARE @term2 VARCHAR(10);

 SET @term1 = 'cat';

 SET @term2 = 'dog';

SET @term3 = @term1 + ' ' + 'OR' + ' ' + @term2;

SET @term4 = '[^ @term1  OR  @term2 ]' ;

SELECT * FROM table WHERE CONTAINS(column, @term3) <--up to this point works
AND (column NOT LIKE '%[@term4]%'); <--this part doesn't include the second term (@term2)

If you'd like to fiddle with this on your end, my Full Text Indexed table looks like:

 animalListID     AnimalsList
       1           cat dog
       2           cat
       3           cat dog bird

(basically i need the parameterized version of the SELECT statement which returns the rows with 'cat dog' and 'cat' and NOT 'cat dog bird')

**This is a VERY oversimplified version of my data, but the question is an exact match of the concept I'm trying to achieve. The table will have millions of rows, and many terms in each row.

Rachael
  • 1,965
  • 4
  • 29
  • 55
  • Are you only going to have two terms? Because there are going to be only 4 different combinations 'dog', 'cat', 'dog cat', 'cat dog' so you can create 4 combinations and just do an exact match search. – Jose Chama Oct 26 '12 at 17:49
  • no, this is in a full text search HUGE table with millions of rows. I want exact matches of only terms in the contains statement (/no rows with anything other than those terms). The parameters are going to be different each time the search is conducted (hence why I didn't stop at the `CONTAINS()`) – Rachael Oct 26 '12 at 17:52
  • Yes, but you can use the contains first and then the combination with an AND. I just posted an answer that might work. – Jose Chama Oct 26 '12 at 17:55

1 Answers1

2

You should do

SET @term4 = '[^ ' + @term1 + ' OR ' + @term2 + ' ]';

and

column NOT LIKE '%' + @term4 + '%'

Alternatively, you could initialize @term4 to also include the % signs:

SET @term4 = '%[^ ' + @term1 + ' OR ' + @term2 + ' ]%';

and then simplify the LIKE statement to:

column NOT LIKE @term4
Cristian Lupascu
  • 39,078
  • 16
  • 100
  • 137
  • I'm glad it works. You need to be careful: in T-SQL, `'@term1'` represents that string literally, not the value of the `@term1` variable – Cristian Lupascu Oct 26 '12 at 18:23
  • You know, I actually thought I was "escaping" that with the brackets. There was definitely a gray area with the brackets and parameters. I'm new to the "^" wildcard and escape brackets--so useful. Can't believe I didn't know of it before. – Rachael Oct 26 '12 at 18:33