SQlite and scandinavian characters

[ibe]

How should I store scandinavian characters of strings (öäå) inside an SQlite database. If I store them as such from RR I can do exact string searches, ie.
select * from food where name like "pä"
will return all strings starting with pä and Pä, but not pÄ or p
PÄ. So I probably should convert them when storing the values originally.

Is the recommended method to store the values in SQlite as unicode?

Any pointers appreciated.

Thanks,

Ismo

[Janschenkel]

This sounds like a problem in SQLite to me, if its case insensitive sort doesn't work for uppercase/lowercase special characters with diacritics.

The only alternative I can give you, is to store an additional 'normalized' copy of the string in another column of the same table; in that 'normalized' copy, you replace all characters with their uppercase version, stripping off the diacritical part.
So àbçéèöäå would become ABCEEOAA, and naturally you would also have to use a similar 'normalized' version of your search term in your SELECT query. The downside being that it is a bit more work to program, and a search for pä would also turn up pa, pà and so forth - but some people would call this a very useful feature.

HTH,

Jan Schenkel.

[ibe]

Thanks Jan, I've been working on this and found a solution that works for me. If I store the strings as ISO (I do a MacToISO before storing and ISOtoMac after I retrieve it) then everything works for at least scandinavian characters.

So now I can do sql 'like' searches that are case insensitive. Only downside is that I'll have to check if my app is running on a mac and then do the extra string conversions.

My 2 cents,

Ismo