Vietnamese Unicode text search in SQLite

Question

I plan to write an iOS application that uses SQLite as a backend. My database contains Vietnamese text such as "Hải Sơn" . Users used for Google searches want to enter a search term, such as "hai son" , to find the text above. I tried the following query:

SELECT * FROM towns WHERE title LIKE '%hai son%'; 

And I have 0 entries. How to make it work? I know that Google and other search engines handle this case, so this can be done. I also do not want my users to enter Vietnamese text with full diacritical marks, because not all users know how to do this.

Update

I looked through the sqlite3 documentation and there seem to be only three valid sorting sequences: BINARY, NOCASE and RTRIM. Did I miss something?

Additional Information

My table was created using

 CREATE TABLE towns ( sid INTEGER PRIMARY KEY NOT NULL, title TEXT ) 

So far, I have used the sqlite3 command line to create a database, table, and import text from a CSV file.

My sqlite3 is in version 3.7.12

Update 2

An alias gave me an idea: create your own sort sequence. I will publish a report if it works.

Answer 1

Try the following:

 SELECT * FROM towns WHERE title COLLATE UTF8CI LIKE '%hai son%'; 

Related to the answer found here: How to convert a column to ASCII on the fly without saving to check for matches with an external ASCII string?

Answer 2

I know this is an old thread, but I found a solution by sending it to others. If you are using sqlite3 library, you can use sqlite3_bind_text()

as below

 ... sqlite3_stmt *detailStmt; NSString * t1=@ "%%Hải Sơn%%"; const char *sql = "SELECT * FROM towns WHERE title LIKE ?"; if(sqlite3_prepare_v2(database, sql, -1, &detailStmt, NULL) == SQLITE_OK) { sqlite3_bind_text(detailStmt, 1, [t1 UTF8String], -1, SQLITE_TRANSIENT); ...