CODEWITHSUNDEEP
sqliteselect
xardas_the_mage
xardas_the_mage

Reputation: 43

SQLite select-string with umlaut

Here's a simple problem but I can't solve it alone since I'm not really familiar with SQL.

Most of you may already know this, in German there are umlaut-letters, e.g. "Ä,Ö,Ü", the lower case of them would be "ä,ö,ü".

I'm using a sqlite-database, accessing it with the Firefox plugin "SQLiteManager".

My select statement looks like this:

SELECT * FROM Projects WHERE Token LIKE '%ä%'

The Firefox plugin and also a SQLite library for .NET both return the wrong output. They return not only the entries with the lower case "ä", but also the entries with the upper case "Ä".

Do you guys know a simple solution to this?

Upvotes: 3

Views: 1988

Answers (2)

Jochen Schwarze
Jochen Schwarze

Reputation: 292

This is a very inconvenient workaroud which does not make queries faster, but it does the trick. I replace all uppercase german umlauts after lowering my test_string like this:

SELECT replace(replace(replace(lower('ÄAÄBÖOÖGDDÜUÜ'), 'Ä', 'ä'), 'Ü', 'ü'), 'Ö', 'ö') AS lowered

lowered
---------
äaäböoögddüuü

Upvotes: 0

CL.
CL.

Reputation: 180080

The documentation says:

SQLite only understands upper/lower case for ASCII characters by default. The LIKE operator is case sensitive by default for unicode characters that are beyond the ASCII range.

But:

The ICU extension to SQLite includes an enhanced version of the LIKE operator that does case folding across all unicode characters.

Upvotes: 3

Related Questions