Eric Bohlman <[EMAIL PROTECTED]> wrote: > Dennis Cote wrote: > > You could also do this: > > SELECT x from y WHERE y.x LIKE '%' || ? || '%' ; > > > > The || operator concatenates the % characters with your string. Now you > > don't need to massage the string in the calling code. Six of one, half > > dozen of the other. > > Note, though, that as currently implemented (DRH has said it might > change in the future) the concatenation will be performed for each row > evaluated (and that particular query will guarantee a full-table scan) > so doing it in the calling code would be a better idea if large tables > are involved. >
The full table scan will happen regardless of what you do if you are LIKE-ing with anything that starts with '%'. But you can force the common subexpression elimination by putting the subexpression in a subquery: SELECT x FROM y WHERE y.x LIKE (SELECT '%' || ? || '%') The subquery will do the concatenation exactly once and reuse the result for every row of the Y table that it scans. -- D. Richard Hipp <[EMAIL PROTECTED]>
