A workaround would be to duplicate the column and fill the second with upper case text without accent, but it is not very elegant !Hello Jibe
search * from some_table where normalize(name) like '%jeremy%';
But as far as I know, with SQLITE, we have to create a special collation function, which does not works with LIKE, so we have also to create a special LIKE function.
But you'll still need to write the code to strip the accents.
Thanks for your reply, but did you noticed well that I am working with SQLITE and that I mentioned
Quote:But as far as I know, with SQLITE, we have to create a special collation function, which does not works with LIKE, so we have also to create a special LIKE function.
Is it what you mean when you say
Quote:But you'll still need to write the code to strip the accents.
My problem is that I don't know exactly what to do, how to write this special collation function inside the SQLITE code and how to modify the LIKE funtion to use it...
I forgot to say : trying your code, Honza, I got the error "no such function: normalize"Yes, "normalize" would be the UDF written by yourself. Any C function can be added to sqlite using sqlite3_create_function(). The code looks something like this (cut out from one of my older projects):
extern "C" { void sqlite_md5(sqlite3_context *context, int argc, sqlite3_value **argv) { String md5 = MD5StringS((const char*)sSqlite->sqlite3_value_text(argv[0])); sSqlite->sqlite3_result_text(context, md5.Begin(), md5.GetCount(), SQLITE_TRANSIENT); return; } } void RegisterSqliteFunctions(Sqlite3Session& session){ ::sqlite3* db = (::sqlite3*)(Sqlite3Session::sqlite3*)session; sqlite3_create_function(db, "md5", 1, SQLITE_ANY, 0, (void*)sqlite_md5, 0, 0); }
/home/joseph/uppMyApps/Owl/main.cpp (432): error: invalid conversion from 'void*' to 'void (*)(sqlite3_context*, int, sqlite3_value**) {aka void (*)(sqlite3_context*, int, Mem**)}' [-fpermissive] /home/joseph/upp/uppsrc/plugin/sqlite3/lib/sqlite3.h (4487): note: initializing argument 6 of 'int sqlite3_create_function(sqlite3*, const char*, int, int, void*, void (*)(sqlite3_context*, int, sqlite3_value**), void (*)(sqlite3_context*, int, sqlite3_value**), void (*)(sqlite3_context*))'
extern "C" { void Sqlite3NoAccents(sqlite3_context *context, int argc, sqlite3_value **argv) { String str = RemoveAccents((const char*)sqlite3_value_text(argv[0])); sqlite3_result_text(context, ~str, str.GetCount(), SQLITE_TRANSIENT); return; } } Sqlite3Session sqlite3; ::sqlite3* sqlite3db = (::sqlite3*)(Sqlite3Session::sqlite3*)sqlite3; sqlite3_create_function(sqlite3db, "NoAccents", 1, SQLITE_ANY, 0, (void *)Sqlite3NoAccents, 0, 0);
extern "C" { void Sqlite3NoAccents(sqlite3_context *context, int argc, sqlite3_value **argv) { String str = RemoveAccents((const char*)sqlite3_value_text(argv[0])); sqlite3_result_text(context, ~str, str.GetCount(), SQLITE_TRANSIENT); return; } } void (*pSqlite3NoAccents)(sqlite3_context *, int, sqlite3_value **); Sqlite3Session sqlite3; ::sqlite3* sqlite3db = (::sqlite3*)(Sqlite3Session::sqlite3*)sqlite3; sqlite3_create_function(sqlite3db, "NoAccents", 1, SQLITE_ANY, 0, pSqlite3NoAccents, 0, 0);
/home/joseph/uppMyApps/Owl/book.cpp (2891): error: 'NoAccents' was not declared in this scope
w = (Like ((SqlId)NoAccents((SqlId)("TITLE")),"%" + AsString(~str) + "%")); book.Query(w);