Queries. Question to all

[Ruslan Zasukhin]

On 6/30/04 12:20 PM, "Peter Knobloch" <peter.knobloch at absus.at> wrote:

Hi Peter,

> I'm using REALbasic (cross platform) and Valentina, maybe I'm overlooking
> something.
> 
> I want to search for the left part of words (not case-sensitive) in my
> database-application:
> 
>   (1) To have an index based search i have to use "... WHERE
> left(fieldname, charcount) = 'searchword'"
> 
>   (2) The field has to have the flag IndexByWords set to TRUE
> 
>   (3) This kind of search is always case-sensitive
>   
> The last point (3) is really troublesome, I can't remember any other
> database (FileMaker, mySQL, V12) I've used where this was the default
> behaviour, especially since I could not find any way to override it.

I am not sure about mySQL

To get case-insensetive search you should create virtual field (BaseObject
method) 

    fldLwr with formula "fld"


Now you can do

    WHERE left( fldLwr, count ) = 'smallletters'

BaseObject methods is VERY POWERFUL to do any tricks.


> Furthermore, according to my experience, I can not imagine too many
> situations where I would need a case-sensitive search.

Ok, but then please remind me, how in mySQL you will be able
    IF YOU WANT provide case-sensetive seach?

I have CC this to Valentina list.
For several years nobody have complain that Valentina do on default
case-sensetive search. What you think guys ?

Should we change this in 2.0 ?
I think no.


>   (4) The only way to do a case-insensitive "word-start" search is to
> use the RegEx variant
>       "... WHERE fieldname LIKE '\Asearchword'"

No, BaseObject methods is a solution.


> The disadvantage is that, this way the search does not use the index
> (since my application should run off a CD-ROM I try optimize for
> performance everywhere) and (the bigger problem) it didn't work at all (=
> it found not a single record, but I'm sure You will tell me what I did
> wrong. I use a query with several joins, "... WHERE fieldname LIKE
> 'searchword'" did work).

Hmm, you mean you do in query multi-table search?

Does it work for you on single table ?


-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------