Search for string, ignoring accents

Question

I use Reqex for pattern search. I used csv data, but data get to match to handle in csv. I chose SQLite as database. I need to make a pattern search to extract only this data. Is it possible with sqlite query too?

string filter = key.Replace("u", "(u|ü)").Replace("a", "(a|ä)").Replace("o", "(o|ö)").Replace("s", "s|ß").Replace("ss", "(ss|ß)");
            var myregex = new Regex(@"\b" + filter);

Update:

I have different values in my database:

An der Muhle
An der Mühle

In both cases, if the the user search for "Muhle" I have to find "Mühle" too. I used above regex patternsearch and is work well, but now i have to same with sql query.

Could you share what is your key and what are you trying to achieve? — Tomáš Kratochvíla, Jul 12 '16 at 07:10
In SqlServer it is possible to specify "accent insensitive". Could that be possible in sqlite also? — Hans Keﬆing, Jul 12 '16 at 07:44
Yes, for example see the following answer: http://stackoverflow.com/questions/14009261/sqlite-accent-insensitive-search — Tomáš Kratochvíla, Jul 12 '16 at 07:46

Tomáš Kratochvíla · Answer 1 · 2016-07-12T07:47:57.510

1

You can adapt your filter and use it in SQL query:

string filter = key.Replace("u", "[uü]").Replace("a", "[aä]").Replace("o", "[oö]").Replace("s", "[sß]");
var SQLQuery = @"SELECT * FROM * WHERE key="+filter;

edited Jul 12 '16 at 07:47

answered Jul 12 '16 at 07:13

Tomáš Kratochvíla

723
4
19

Not sure how to properly match `ss`or `ß` in SQL. Your original code for a key: `"ss"` generates the filter: `"s|ßs|ß"`. – Tomáš Kratochvíla Jul 12 '16 at 07:53

score 0 · Answer 2 · answered Jul 12 '16 at 07:59

One way to do it is registering a custom collation... this could get you started (totally untested!):

[SQLiteFunction(FuncType = FunctionType.Collation, Name = "NORMALIZEDCI")]
public class SQLiteNormalizedComparer : SQLiteFunction 
{
    private static string RemoveDiacritics(string text)
    {
        var normalizedString = text.Normalize(NormalizationForm.FormD);
        var stringBuilder = new StringBuilder();

        foreach (var c in normalizedString)
        {
            var unicodeCategory = CharUnicodeInfo.GetUnicodeCategory(c);
            if (unicodeCategory != UnicodeCategory.NonSpacingMark)
            {
                stringBuilder.Append(c);
            }
        }
        return stringBuilder.ToString().Normalize(NormalizationForm.FormC);
    }

    public override int Compare(string x, string y) 
    {             
        return string.Compare(RemoveDiacritics(x), RemoveDiacritics(y), StringComparison.OrdinalIgnoreCase);
    }
}

And then to use it, you need to register it before you open up your database:

SQLiteFunction.RegisterFunction(typeof(SQLiteNormalizedComparer));

Once this is done, you can add that collation to your table, for example:

CREATE TABLE `myTable` (
  `Id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  `Name` TEXT NOT NULL COLLATE NORMALIZEDCI)

Again, this is all untested... I used snippets I had from here and there to make this answer, but I'd not expect copy & paste would work. I don't really have an environment here where I can test this.

Also, rather than having my own collation for a table (which might make it not portable), I'd rather have an additional "search" column, where you can insert normalized (with diacritics removed, for example) strings, so you use that for search, and the original for display. This effectively almost doubles your storage requirements, but SqLite databases are generally small, so your choice here.

Search for string, ignoring accents

2 Answers2