CODEWITHSUNDEEP
sqlinternationalizationandroid-sqlitesql-like
Leonid Semyonov
Leonid Semyonov

Reputation: 493

SQLite LIKE - cannot (== doesn't know how to) convert not-English letters to the same case

Sorry for my English

Text columns in my database contain English and not-English letters.

So I cannot retrieve (using LIKE) rows whose Title column meets (case-insentive) some pattern:

SELECT * FROM TableName WHERE TitleColumn LIKE '%pattern%';

or even:

SELECT * FROM TableName WHERE UPPER(TitleColumn) LIKE UPPER('%pattern%');

For example. If field Title contains "ГазПром", and pattern is "газ" (Г and г are the same letter in different cases), then neither of the two SELECT statements above cannot recognize a match.

What can help me?

Upvotes: 0

Views: 102

Answers (1)

Paweł Dyda
Paweł Dyda

Reputation: 18662

Usually when you want to compare something case-insensitive, or you accent insensitive (i.e. á vs. a), you specify the collation using COLLATE keyword:

SELECT * FROM TableName WHERE TitleColumn LIKE '%pattern%' COLLATE 'German_CI_AI_SC';

Unfortunately, given its documentation I don't think SQLite supports COLLATE in selection, and at the same time it does not seem to support language-specific collations.
Please keep in mind, that case-insensitive comparisons require you to specify the language, as it case conversion rules differ between various languages...

Upvotes: 1

Related Questions