Sargable no-op full text search value?

[former_SQLA_member1694911]

Is there a value that you can set the contains-query-string in a CONTAINS search condition so that the condition is equivalent to a constant true and which the optimizer knows is constant true?

As a comparison, LIKE '%' matches everything and the optimizer knows it, and takes that into account in the plan analysis.

I don't actually need this, and know how to code around it, but it's a nice convenience when coding user interfaces in which the user can search on various subsets of lots of criteria, and especially when doing this with ODBC's ? parameters.

[MarkCulp]

I do not believe that there is a term equivalent to what '%' does in the LIKE clause.

I think what will likely work for you is to combine the CONTAINS clause with another term which the optimizer will know definitely its value and hence can optimize out the CONTAINS clause if needed.

Example: I believe the CONTAINS predicate in the following will be optimized out of the query since 1=1 is always TRUE:

SELECT *
  FROM T 
 WHERE ( 1=1 OR CONTAINS( T.col, @variable ) )
   AND ...other-predicates...

where as it will not in the following query since 1=0 is always FALSE:

SELECT *
  FROM T 
 WHERE ( 1=0 OR CONTAINS( T.col, @variable ) )
   AND ...other-predicates...

You can also likely use a variable condition in the place of 1=0 and 1=1 as in:

SELECT *
  FROM T 
 WHERE ( @use_contains_on_col=0 OR CONTAINS( T.col, @variable ) )
   AND ...other-predicates...

and still get the required effect provided that @use_contains_on_col has a known value at statement describe and open time. I.e. set @use_contains_on_col to 1 or 0 prior to opening the cursor on your query.

Note that I have not confirmed that the above works and it may or may not work for you depending on exactly which version of SA you are using.

[Former Member]

Just to add - host variables cannot be used as CONTAINS arguments, so connection variables - as suggested by Mark - will have to be used. CONTAINS query has to be a value known at query open time.