[yunqa.de] Re: How do I perform an accent insensitive compare
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Wed, 24 Oct 2012 09:26:31 +0200
On 24.10.2012 08:36, Yannick SILVA wrote:
> I try to use the collate SQL_Latin1_General_CP1_CI_AI to make a accent
> insensitive compare but it failed with exception class ESQLite3 Error 1
> - no such collation sequence : SQL_Latin1_General_CP1_CI_AI
>
> I took the reference on this web site :
> http://stackoverflow.com/questions/2461522/how-do-i-perform-an-accent-insensitive-compare-e-with-e-e-e-and-e-in-sql-ser
>
> Anyone knows how to solve this or to adapt it for Delphi/Disqlite3 ?
>
> Here is the kind of query I do :
> SELECT * FROM myTable WHERE name LIKE "%cafe%" COLLATE
> SQL_Latin1_General_CP1_CI_AI
There are two important points to your question:
1.
The "SQL_Latin1_General_CP1_CI_AI" collation is unknown to SQLite.
SQLite has three built-in collating functions: BINARY, NOCASE, and RTRIM
which are always available. Additional collation sequences may be
associated with a database connection using the
sqlite3_create_collation() function. See the DISQLite3_Create_Collation
demo for an example.
2.
SQLite does not apply collations to the LIKE operator. So even if you
register your own accent insensitive collation and add it to your like
operator query, SQLite will NOT use it. Please read the collation
documentation referenced sqlite3_create_collation() for when and how
SQLite applies collations.
Solutions:
1.
SQLite implements the LIKE operator by means of a function named "like".
You can replace this function to implement your own handling of LIKE by
registering a new function with the same name using
sqlite3_create_function().
2.
You can also write a custom function (say, "normalize") that would
return its argument lowercased and with accents removed. Then you can do
SELECT * FROM myTable WHERE normalize(name) like '%cafe%';
The use of sqlite3_create_function() is demonstrated in the
DISQLite3_Create_Function() example project.
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
