Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 8 of 8 total
Thread LIKE filter and accents
Thu, Apr 27 2006 7:35 AMPermanent Link

"Santy Concepción"
Hi!

I'm using DBISAM 4.23

I want to filter a table as follows:

   text := 'a';
   table1.filter := 'UPPER(NAME) LIKE UPPER(''%' + text + '%'')';

I want to show every records that contains "a", "A", "á", "Á", "à", etc...
but it only shows the ones without accents ('a' and 'A').

Any help?
Should I change the table 'Locale' or index type?

Thanks!

Thu, Apr 27 2006 9:25 AMPermanent Link

Dan Rootham
Santy,

<<I want to filter a table as follows:
   text := 'a';
   table1.filter := 'UPPER(NAME) LIKE UPPER(''%' + text + '%'')';

I want to show every records that contains "a", "A", "á", "Á", "à", etc...
but it only shows the ones without accents ('a' and 'A'). >>

For you this may not be the best way to handle it, but I can tell you how we solved
exactly this problem for a multilingual dictionary. The queries were being sent to
a DBISAM database through mobile phone networks, so we didn't even have any
guarantee that accented characters would arrive intact!

We created an extra column on each dictionary-language table. This new column
contained a version of the search column, with these changes:
- truncated from 100 characters to 20 characters
- forced to uppercase
- all accents stripped off

This new column was then indexed and used as the lookup for all searches.
Obviously this means that you need to "preprocess" any table to create the
new accent-free index. And it also means that in real time you must "strip accents"
from any search term and uppercase it before the term is submitted as a
parameter to a SQL query. We just wrote a language-aware Delphi unit to do this.
Why language-aware? Because some languages are different: for German, we
wanted to change a-umlaut to "ae" rather than to "a".

But the extra work is worth it, believe me. We found that this solution worked
perfectly for many West European languages. We could do a round-trip translation
from cellphone to DBISAM and back to the user's cellphone in around 5 seconds -
even with the accent-stripping function included before the SQL query.

HTH,
Dan

Lexicon Software Ltd
Thu, Apr 27 2006 12:01 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Santy,

<< I want to show every records that contains "a", "A", "á", "Á", "à",
etc...  but it only shows the ones without accents ('a' and 'A').

Any help?
Should I change the table 'Locale' or index type? >>

What is the current Locale for the table ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Thu, Apr 27 2006 12:45 PMPermanent Link

"Santy Concepción"
> What is the current Locale for the table ?

"ANSI Standar"

>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Thu, Apr 27 2006 12:47 PMPermanent Link

"Santy Concepción"
If I set the table Locale to "Spanish", then it works fine, BUT...

Will I have any problems changing the table Locale? Any problems
migrating/converting from paradox?
What are the advantages/disadvantages/differences of setting the Locale to
Spanish or ANSI Standar?

Thanks!

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> escribió en el
mensaje news:593EF802-BECD-4547-B2B6-499DD3983D9D@news.elevatesoft.com...
> Santy,
>
> << I want to show every records that contains "a", "A", "á", "Á", "à",
> etc...  but it only shows the ones without accents ('a' and 'A').
>
> Any help?
> Should I change the table 'Locale' or index type? >>
>
> What is the current Locale for the table ?
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Fri, Apr 28 2006 3:57 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Santy,

<< If I set the table Locale to "Spanish", then it works fine, BUT...

Will I have any problems changing the table Locale? Any problems
migrating/converting from paradox? >>

Nope, the only difference is that the collation/sorting of indexes and ORDER
BYs is done according to Spanish collation rules.

<< What are the advantages/disadvantages/differences of setting the Locale
to Spanish or ANSI Standar? >>

There are really no differences at all, other than the overhead of string
comparison calls to the OS for Spanish collation/sorting.  But, I doubt if
you'll even notice the difference.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sat, Apr 29 2006 6:34 AMPermanent Link

"Santy Concepción"
Hi, Tim...

Thanks for your answers.

How can I get the Spanish 'LocaleID' form creating the tables?
What about if one of my customers doesn't have the spanish LocaleID in his
computer?
Or how can I be sure that the "LocalID" number is always the same in every
computers?

Thanks!


"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> escribió en el
mensaje news:E2C125D9-B960-4CEB-AC0A-1B95C6C850B1@news.elevatesoft.com...
> Santy,
>
> << If I set the table Locale to "Spanish", then it works fine, BUT...
>
> Will I have any problems changing the table Locale? Any problems
> migrating/converting from paradox? >>
>
> Nope, the only difference is that the collation/sorting of indexes and
> ORDER BYs is done according to Spanish collation rules.
>
> << What are the advantages/disadvantages/differences of setting the Locale
> to Spanish or ANSI Standar? >>
>
> There are really no differences at all, other than the overhead of string
> comparison calls to the OS for Spanish collation/sorting.  But, I doubt if
> you'll even notice the difference.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Mon, May 1 2006 2:37 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Santy,

<< How can I get the Spanish 'LocaleID' form creating the tables? >>

Are you asking how to specify the LocaleID while creating the tables ?  If
so, then see the docs for creating tables:

http://www.elevatesoft.com/dbisam4d5_creating_altering_tables.htm

<< What about if one of my customers doesn't have the spanish LocaleID in
his computer? >>

As long as they're using Windows 2000 or higher, they should have Spanish
language support already installed.  Windows 9x might require some
additional installation from the Windows CD.

<< Or how can I be sure that the "LocalID" number is always the same in
every computers? >>

Windows locale IDs are standard across all versions of Windows and are
always the same.

--
Tim Young
Elevate Software
www.elevatesoft.com

Image