accent insensitive diacritic insensitive data retrieval

[vinhat]

Hi,

Is there any option / feature in DB2 database where one can retrieve
data from the database by treating the accented / diacritic characters
equivalent to their English characters. For example I would like "à"
is treated equivalent to "a" or "é" is treated equivalent to "e" so
that the SELECT query can retreive data irrespective if it has accented
or non-accented characters.

This is required because we have French and German names in our
database but the support personnel who usually searches the database
has an English keyboard as a result entering the accented characters in
the application is difficult.

Please let me know how could I resolve thhe problem.

Thanks & Regards,
Vinay

[chenbaolin]

Vinay,
Add a new column in the table called S_name. create a UDF to remove all
the accent from the name column and save the result in upper case in
the S_name column.
For example:
If the valumn in name column is 'éééààà', then the value in
S_name is 'EEEAAA'.

In your query, you use column S_name as search column and display
column name. (User enter no-accent string for searching, you conver
string into Upper case first.)

HTH,

Bill

[e-mail] wrote:

Hi,

Is there any option / feature in DB2 database where one can retrieve
data from the database by treating the accented / diacritic characters equivalent to their English characters. For example I would like "à"
is treated equivalent to "a" or "é" is treated equivalent to "e" so
that the SELECT query can retreive data irrespective if it has accented or non-accented characters.

This is required because we have French and German names in our
database but the support personnel who usually searches the database
has an English keyboard as a result entering the accented characters in the application is difficult.

Please let me know how could I resolve thhe problem.

Thanks & Regards,
Vinay

[vinhat]

Dear Bill,

Thanks for the response, I had already found similar solution in the
db2 user group. But this solution will increase the tablespace
unnecessarily.

Actually I was looking at some database configuration parameter which
could allow diacritic insensitive search (something similar to the one
available in Oracle).

Thanks & Regards,
Vinay