On Sun, Jul 20, 2008 at 07:09:54PM +0200, william sqllite scratched on the wall: > Hi all, > > We're trying to get a big dictionary website running on SQLite (was MySQL), > but we ran into some trouble that we can't really seem to fix. > > While we're using UTF-8 coding, a query like > > SELECT * FROM language WHERE word like '%o%' > > doesn't find words with Ö or ö, while it did in MySQL. Also the case with > for example a for àáâãä and å. > > Is it just a simple configuration thing maybe? Or is sqlite just not ready > for these kind of searches?
Known issue. From http://www.sqlite.org/lang_expr.html on the 'LIKE' operator: SQLite only understands upper/lower case for 7-bit Latin characters. Hence the LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 characters. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.) This isn't the exact problem you're having, but the point is that SQLite is not very Unicode aware, beyond correctly supporting encoding and decoding. And that's to be expected, given the huge complexities of dealing with different languages. The good news is that you can re-implement the LIKE function fairly easily. There have been a number of posts in the past dealing with using external Unicode/I18N libraries to implement a more complete 'LIKE' function. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users