Search for data with accented character

Databases
1

Hi,

i have a database for client with first name and last name that might have accented characters. I have a search screen to enter part of first or last name and a find button to look for record and show in the listbox.

I want to able to type ‘Koc’ to look for record with ‘Ko’ or type ‘Francois’ to look for ‘Franois’.

2

Which database do you use?

In Valentina this is handled via the collation settings. See http://www.valentina-db.com/docs/dokuwiki/v5/doku.php?id=valentina:products:vstudio:help:schema_editor:column_view . As far as I remember for the accents this should be Strength: kSecondary.

I don’t know how this is handled in other databases but it’s a complicated topic. The databases need to support a library from IBM called ICU.

3

i am using SQLite and CubeSQL

4

Seems it may be addressed with Collate.

http://stackoverflow.com/questions/4024072/how-to-remove-accents-and-all-chars-a-z-in-sql-server

5

I don’t know if this will work with Xojo’s SQLiteDatabase:

SELECT icu_load_collation('de_DE', 'GERMAN'); ... an SQL statement ... COLLATE GERMAN;

6

The engine should be compiled with all support provided. The only default “collation” functions SQLite provide are BINARY (the default), NOCASE and RTRIM. The implementors of the engine can “create” more and add them through the sqlite3_create_collation() interface but it will make your SQL code non portable. ICU extensions are better for cross compatibility (IF the other side have ICU), but makes the engine fat and if I am not sure Xojo has it implemented/enabled.

7

[quote=112003:@Richard Duke]I want to able to type ‘Koc’ to look for record with ‘Koç’ or type ‘Francois’ to look for ‘François’.
[/quote]

The database contains ‘François’, but I see no simple way for a program to convert ‘Francois’ into it, apart from some sort of spell checking stuff.

Upon save, you could duplicate the “First name” field and use a replacement algorithm to create a “First name without accents” field. Then you search into “First name without accents” the way you describe, then use the found index to display “First Name”. If entries can be done with accents as well, you need to detect if accents have been used, and then search in “First name” instead.

8

i found the solution!!! i just purchase the mbs sqlite plugin and use these instead

SELET * FROM tblClient WHERE RemoveAccentsMBS(first name) LIKE ‘%Francois%’