>> myfield like "%FORD%" ;
>> and not myfield like "%_FORD%" ;
>> and not myfield like "%FORD_%"
>>
>>will allow FORD in any way except as a part of a word (no characters
>>before it, and no characters after).
>>
>>It works (tested). Should be better optimizable than checking for left
>>and right edges of the field.
>
>Thanks, Dragan --
>Someone said there are 3 ways to do everything in FP; I guess we now have
>three. I since have discovered that just leaving off the '%' on the left
>and right works also:
>
>where myfield like '%FORD ';
>or myfield like '% FORD %';
>or myfield like ' FORD%'
>
Sure it works? I've tried it against our zipcode table with "NOVI"
(should find NOVI BEOGRAD, NOVI SAD, HERCEG NOVI and should omit
BANOVIÆI and the like). It omitted all the records where first word was
NOVI.
What we both probably missed here, is the fact that % assumes there is
something non-blank which may come instead of it, so it can't stand for
the left or right edge of the character field. Our example should really
look like this
where myfield like '%FORD ';
or myfield like '% FORD %';
or myfield like '% FORD';
or myfield like 'FORD %';
or myfield like ' FORD%'
The newly added 3rd and 4th OR clauses cover for right and left edges of
the field... which is far from elegant etc etc.
Then I tried various other SQL combinations, and the clumsiest and most
ridiculous worked best:
where ' FORD ' $ ' '+myfield+' ' :)))
I know it's worse than "not optimizable", but it gives you exactly what
you need.
>))) is my comment and is not part of the proper syntax.
