case-insensitive searches

[Ruslan Zasukhin]

On 7/1/05 11:00 PM, "Charles Yeomans" <yeomans at desuetude.com> wrote:

>>>>> I've got a text field that is indexed by words.  In V4Rb 1, to do a
>>>>> case-insensitive search I needed to define a method field and search
>>>>> it.  Is this still the case in V4Rb 2, or can I now do something
>>>>> like
>>>>> 
>>>>> SELECT textField from ATable WHERE Lower(textField)="foo" ?
>>>> 
>>>> This should work in 2.0.
>>> 
>>> It turns out that it doesn't work.  Here, textField is a VarChar field
>>> that is indexed by words.  I tried making queries with VStudio as
>>> well.
>>>   Other SQL string functions work; for instance,
>>> 
>>> SELECT textField from ATable WHERE Left(textField, 3)='foo'
>>> 
>>> works.  But other functions do not --
>>> 
>>> SELECT textField from ATable WHERE Reverse(textField)='oof'
>> 
>> Ivan.
>> 
>> Please check this RIGHT NOW
>> 
>> in vstudio make small test db
> 
> I just made a small test db using VStudio, and queries of the type
> above didn't work.

Well, 

It looks that you expect that IF field is indexed by words, than

    SELECT textField from ATable WHERE Lower(textField)="foo"

Will find you record with "foo too roo doo" ?

NO.

    Because Lower(textField)='foo'     // by the way, single quotes
    will not do indexed search.
    it will do simple SCAN of column.

    and it can find only record with "foo", "FOO", "fOO" values


-- 
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]