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.
answered
13 Aug '10, 02:16
Mark Culp
24.9k●10●139●297
accept rate:
41%