Irshad Alam wrote:
> I want to use WHERE Clause for unicode search in database. Let me clear more
> about my requirement. Suppose I've a database whose table name is employee
> having name, age and salary as three columns.
>
> Now I want to make query on employee table which gives the result having
> names start between 'D' and 'M', so probable I'll do something " SELECT *
> FROM employee WHERE name BETWEEN 'd%' AND 'n%' ". But this is very specific
> if I know the characters. I want to avoid using the character and use
> unicode for making the query which means rather than using 'd%' and 'n%', I
> wish to use unicode values[D --> U+0044] and [M --> U+004d].
Something like this:
char* sql = "select * from employee where name >= ? and name < ?";
sqlite3_stmt* stmt;
sqlite3_prepare(db_handle, sql, -1, &stmt, NULL);
wchar_t first_character[2] = {0};
wchar_t last_character[2] = {0};
first_character[0] = 0x0044;
last_character[0] = 0x004d + 1;
sqlite3_bind_text16(stmt, 1, first_character, -1, SQLITE_STATIC);
sqlite3_bind_text16(stmt, 2, last_character, -1, SQLITE_STATIC);
// Run statement here.
Note that Unicode collation is not as simple as you might think. Did you know
that in Estonian, 'y' sorts between 'i' and 'j'? Or that in German phonebook
sort, 'oe' sorts as if it were a single letter between 'o' and 'p'? Basically,
your simplistic approach would only work for plain unaccented Latin letters and
English collation rules.
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
