case-insensitive searches

[Charles Yeomans]

On Jul 1, 2005, at 6:10 PM, Ruslan Zasukhin wrote:

> 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

I expect that Valentina can do anything :)
But for now I will use method fields as I have been.

--------------
Charles Yeomans