Vietnamese Unicode Text Search in SQLite

Question

I am planning to write an iOS app which uses SQLite as the backend. My database contains Vietnamese text such as "Hải Sơn". The users, being used to Google search, want to enter a search term like "hai son" in order to find the text above. I tried the following query:

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

And I got 0 record. How do I make this to work? I know that Google and other search engines handle this case, so it can be done. I also don't want to make my users type the Vietnamese text with complete diacritical marks because not all the users know how to do so.

Update

I looked at the sqlite3 documentation and looks like there are only three valid collation sequences: BINARY, NOCASE, and RTRIM. Am I missing something?.

More info

My table was created with:

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

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

My sqlite3 is at version 3.7.12

Update 2

Alias gave me an idea: to create my own collating sequence. I will post a follow up if it works.

Coligny

Answer 1

Try this:

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

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

Sherrilsherrill

I tried your solution from the command line version (sqlite3, from Mac OS X) and got the following error: Error: no such collation sequence: utf8_general_ci – Coligny

What if you try replacing that with COLLATE UTF8CI? – Sherrilsherrill

@HaiVu - It looks like this is a common issue for people using SQLite3. Something that has come up in several posts is to add another column in the table with the ASCII version of the text. Here is a good discussion SQLite Create function / non-ascii case insensitive. – Sherrilsherrill

Answer 2

I know this is old thread, but I found solution so posting it for others. If you are using sqlite3 library you can use sqlite3_bind_text()

like 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);
...

Numismatology