>where myfield like 'FORD %'; >or myfield like '% FORD %'; >or myfield like '% FORD' >>as you have below. But doesn't this cover all cases? It does - the left, middle and right. In my 'corrected' example, the fifth line is unnecessary (unless you have some records where there's exactly one space before FORD at the leftmost position), and the first line covers the case with exactly one space after FORD at the rightmost. When we cut that out, we get what you just wrote. >>
>> where myfield like '%FORD '; >> or myfield like '% FORD %'; >> or myfield like '% FORD'; >> or myfield like 'FORD %'; >> or myfield like ' FORD%' >>>Results from test: > >1) All the LIKE code missed the simple case where myfield = 'FORD' That's >enough to disqualify it already... >2) The '$' search was slightly faster at pulling 500 recs out of 10,000, >about .21 to .23 for LIKE on average >Conclusion: The '$' method is faster and more accurate. It's actually kind >of elegant in an odd sort of way... :~)) ad 1) I should have guessed. The "%" expects something non-blank. If there's only "FORD" in the field (left, right or middle), there's nothing around it, so the LIKE clause finds nothing. If it's a feature, it's not really documented, and I'd rather call it a bug. ad 2) I really expected that the $ trick should work some 20% slower, but then... another idea. If you'll need this sort of thing real often, maybe you could index on " "+myfield+" " and see if it's getting any faster (I doubt it, but may be worth one try).