Fastest way to seach for a a string inside a VVarChar field

[Ruslan Zasukhin]

On 1/26/09 11:13 PM, "Stan Busk" <maxprog at mac.com> wrote:

Hi Stan,

 
> I have a table with a VVarChar field that contains list of values that
> way '<1> <2> <3> <4> <5>...<9999999>'
> It is indexed by word.
> 
> To look for a single value like '<3>' I simply use LIKE '%<3>%' or
> REGEX '<3>'.
> Which is faster? Or is it similar?

LIKE can be little faster because simpler algorithm.
But since they do scan of column, in general they are similar


> My second question is, is there a way to look for several values using
> REGEX?
> For example looking for records where my field contains '<1>', '<3> or
> '<5>'?

Yes.  Use operator OR  '|'

    fld REGEX 'a|b|c'
 
> REGEX '<1>|<3>|<5>' seems to be working in Valentina Studio.

right
 
> I am looking for the fastest way to do that as I have to search for
> several of those values in hundreds of thousands of records.

Then best way try to index it
 
> And my very last question, despite all those Regexes work on Valentina
> Studio, should I escape '<' and '>'?
> REGEX '\<4\>|\<\3\>|\<2\>' or it is not necessary?

It seems no need escape  <

Try to Valentina.EscapeString(), and see if it escape anything.
If no -- then no need


---
About index ...

LIKE '%<3>%' or REGEX -- means CONTAINS search.
    index cannot be used in this case.
    
You say you have index by words.

You should try in Vstudio searches as

    WHERE fld = '<3>'

    WHERE fld = '3'

I think one of them will return you result.
Then to get best speed you can do just

    WHERE f in ('<1>', '<3>', '<5>')
or
    WHERE f in ('1', '3', '5' )


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]