Re: [Firebird-devel] Case-insensitive collations

[Alexandre B. S. <ib...@th...>]

Peter Jacobi wrote:

> ...snip...
>
>
>For what use case?
>  
>
Hi Peter/All

Just us the Brazilians want this ??? :-) and the spanishes, mexicans, 
bolivians, frenchs, and all other latin derived languages...

I don't generally post on this list, but this subject are very important 
to me...

I have a lot of customers where the data was pump in my databases from 
legacy applications:

These applications or are DOS based, or even windows based but all 
fields are changed to upper case.

I think that is much more readable, and more elegante to have mixed 
cases texts, and encourage the users of my systems to use it.

But then, they have a problem, if they search for
like "Alexandre%"

he will not macth the legacy records that was imported as "ALEXANDRE"

if he mistypes
"aLexandre" the same occurs.

So case insensitive is important.

Because of this DOS applications or the lazy users don't put correct 
accents, so now he does not know if he should search for
João
or
Joao
or
JOÃO
or
JOAO

Anyway those words are the same name, and every brazillian could know 
and read any of the above variations as the correct one that is
"João"

in commercial applications (that is what I develop) the rule are:
The case/accent does not matter on searching and ordering
the exception is:
The case matters.

The letters 'a', 'á, 'à', 'ã', 'A', 'Á', 'À', 'Ã' should be considered 
the same in compararions and sorting.

Each of my clients asks why the system cares about case (the accent part 
he understands, but the case part no)

I know about proxy columns, but I don't like the idea, I wish to have 
the choice to make a column case/accent insensitive

>The existing multi level collations already implement nearly everything a 
>no-case/no-accent collation can do. Except assuring uniqueness
>regarding columns. But I consider it a somewhat strange design, to 
>a) not allow more than one of "RED", "Red" and "red" in a column
>but 
>b) not to care about which spelling is desired
>
>  
>
I have no experience with multi-level collation, I will read the link 
will sent to Carlos.

AFAIK, the multi-level collation will not work for "like" and "starts 
with" in the majority of the search for names I use starts with, I read 
about your sugestion of using between "something" and "somethingzz", but 
like are much more powerfull... :-(

As I said above in general does not matter if will insert "Red" or "red" 
or "RED", if will put an unique constraint or PK on this column, I must 
be sure that the case variations should not be considered if I define 
this field as case insensitive, and for the user, he will not have 
problens since if he searchs for "red" or "RED" or "Red" the record will 
be found anyway.

> 
>  
>
>>Even an unoficial special Firebird binary was created by a brazilian
>>guy just to implement a case-insensitive collate called PT_BR. Dave
>>was contacted some years ago to develop an official case/accent
>>insensitive collation based on WIN1252 and ISO8859_1. Many people here
>>contributed with money to pay him for this, but due to lack time (from
>>him) the work wasn't done.
>>    
>>
>
>Give me the requirements spec. What exact behaviour do you want for this 
>collation?
>
>  
>
I have contact with this guy, he has on the last days adjusted his patch 
for FB 1.5. he is a member of CFLP (Portuguese Spoken Firebird Community).

His patch can do a case insensitive/accent insensitive search, columns 
with up to 250 chars can be indexed, the "like", "containing" and 
"starts with" works.

>>It seems that FB suffers a collation/charset general mess and I think
>>it deserves a good cleanup in this area.
>>    
>>
>
>IMHO it suffers from some great design ideas and really wonderful 
>generality of approach, but then having stopped development when
>everything was 90% ready.
> 
>  
>
>>In the results of my "Firebird Users Wish List Survey" running at
>>http://infopoll.net/live/surveys/s25748.htm, this feature is among the
>>top 4 mostly wanted features. The final results will be published next
>>week.
>>    
>>
>
>Users don't know how they want to sort ;-)
>
>Regards,
>Peter Jacobi
>
>
>  
>

I will be glad if I can help you to better understand this situation.

see you !

-- 

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br