I recently discovered that the LIKE Tim has built in is to clever for my query generator! I only allow constructs such as xx%, %xx or %xx% ie no wild cards in the middle of the string. This is fine most of the time but if you want to search emails (the full thing not just the message) using the xx% or %xx% patterns then whilst it will do the job its slow. Tim has to allow for collations, and for wildcards in the body of the text string and has to use a sliding window approach for this. For my simple needs I now have

function FldHasStr(const FldStr, CompStr, CI, Mode: string): boolean;
type
TStemMode = (smNone, smStart, smEnd, smBoth);
var
ChkStr: string;
PosInFld: integer;
Stemming: TStemMode;
const
LikeStem = '%';
begin
if FldStr = '' then begin
 Result := False;
 Exit;
end else if CompStr = '' then begin
 Result := True;
 Exit;
end;
ChkStr := CompStr;
if (ChkStr[1] = LikeStem) and (ChkStr[Length(CompStr)] = LikeStem) then begin
 Stemming := smBoth;
 Delete(ChkStr, 1, 1);
 Delete(ChkStr, Length(ChkStr), 1);
end else if ChkStr[1] = LikeStem then begin
 Stemming := smStart;
 Delete(ChkStr, 1, 1);
end else if ChkStr[Length(ChkStr)] = LikeStem then begin
 Stemming := smEnd;
 Delete(ChkStr, Length(ChkStr), 1);
end else begin
 Stemming := smNone;
end;
if (CI = '') or (UpperCase(CI) = 'Y')
 then PosInFld := Pos(LowerCase(ChkStr), LowerCase(FldStr))
else PosInFld := Pos(ChkStr, FldStr);
case Stemming of
 smStart: Result := PosInFld = 1 + (Length(FldStr) - Length(ChkStr));
 smEnd: Result := PosInFld = 1;
 smBoth: Result := PosInFld <> 0;
end;
if (Mode <> '') and (Pos('not', LowerCase(Mode)) > 0) then Result := not Result;
end;

On my test table the difference is from 54 seconds down to 0.2 seconds.


Roy Lambert

Roy,

<< I recently discovered that the LIKE Tim has built in is to clever for my
query generator! I only allow constructs such as xx%, %xx or %xx% ie no wild
cards in the middle of the string.  >>

You should be able to use wildcards in the middle of the string.  If not,
then that's a bug.  Could you confirm that this is what you're saying won't
work:

Column LIKE '%hello%world%'

?

Tim Young
Elevate Software
www.elevatesoft.com

Tim


><< I recently discovered that the LIKE Tim has built in is to clever for my
>query generator! I only allow constructs such as xx%, %xx or %xx% ie no wild
>cards in the middle of the string. >>
>
>You should be able to use wildcards in the middle of the string. If not,
>then that's a bug. Could you confirm that this is what you're saying won't
>work:
>
>Column LIKE '%hello%world%'

Not at all. I'm saying that my query generator only allows those sort of things eg the user selects "starts with" and it generates column LIKE 'xyz%'

Its the fact that you allow % and _ anywhere in the string that makes it, for large CLOBs, and with '%xyz%' or 'xyz%' constructs, like drinking treacle through a straw

Roy Lambert

ps if you want to have a look at the query generator there's a demo in the binaries.

Roy,

<< Not at all. I'm saying that my query generator only allows those sort of
things eg the user selects "starts with" and it generates column LIKE 'xyz%'
>>

Ahh, okay.  Whew !

<< ps if you want to have a look at the query generator there's a demo in
the binaries. >>

I'm going to take a look this weekend when I have some free time.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

Tim


>I'm going to take a look this weekend when I have some free time.

YOU have free time - we'll have to see what we can do about that!

I'm waiting a little while before I post the revised code up to the binaries just in case I get any feedback and need to alter things. I've already fixed a couple of small bugs and altered it so that there's a fallback in case my external functions aren't there.

Roy

Roy Lambert wrote:

<I recently discovered that the LIKE Tim has built in is to clever for my query generator! I only allow constructs such as xx%, %xx or %xx% ie no wild cards in the middle of the string. This is fine most of the time but if you want to search emails (the full thing not just the message) using the xx% or %xx% patterns then whilst it will do the job its slow. Tim has to allow for collations, and for wildcards in the body of the text string and has to use a sliding window approach for this. >

It was a slow day so I started reading old posts in the forum and may have a suggestion. Have you looked at using a Grep function for Delphi?

There is GExperts from http://www.gexperts.org/ which is free (but you need to check the license if this function can be used in a commercial application). There are many other grep functions available for Delphi if you do a google search or look at http://www.torry.net/quicksearchd.php?String=grep&Title=Yes.

I figure if someone has already done the work and optimized the function, it may offer you more capabilities and speed than what you're using now.

Cheers!

Barry