title: SQLite German special chars
body: <p>Some German words contain the "ß" character.</p>
  
  <p>However, sometimes people write "ss" instead and vice versa.
  While not perfectly legal, this is a common practice.</p>
  
  <p>For example, some people for example write "außer", and some write "ausser".</p>
  
  <p>I would like to handle this nicely in my SQLite DB.</p>
  
  <p>Therefore I have created a custom LATIN_NOCASE collation, but it does not work as expected:</p>
  
  <p>I have inserted a word named "außer", and I would like to be able to find it when I search for "s" or "ss".</p>
  
  <p>However, my selection returns no results.</p>
  
  <p>Does anybody see where I went wrong?
  Is a comparision as below enough or do I need to do something additional special?</p>
  
  <p>Thank you.</p>
  
  <pre><code>Imports System.Data.SQLite
  Imports System.Globalization
  
  &lt;SQLiteFunction(FuncType:=FunctionType.Collation, Name:="LATIN_NOCASE")&gt; _
  Public Class SQLiteCaseInsensitiveCollation
      Inherits SQLiteFunction
  
      Private Shared ReadOnly _cultureInfo As CultureInfo = CultureInfo.CreateSpecificCulture("de-DE")
  
      Public Overrides Function Compare(x As String, y As String) As Integer
          Return String.Compare(x, y, _cultureInfo, CompareOptions.IgnoreCase)
      End Function
  
  End Class
  
  Public Class Form1
  
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
  
          SQLiteFunction.RegisterFunction(GetType(SQLiteCaseInsensitiveCollation))
  
          Dim c As New SQLite.SQLiteConnection("Data source=:memory:")
          c.Open()
  
          Using nCmd As SQLite.SQLiteCommand = c.CreateCommand
              With nCmd
                  .CommandText = "CREATE TABLE foo (word TEXT COLLATE LATIN_NOCASE)"
                  .ExecuteNonQuery()
              End With
          End Using
  
          Using nCmd As SQLite.SQLiteCommand = c.CreateCommand
              With nCmd
                  .CommandText = "CREATE INDEX 'idx_foo_word' ON 'foo' (word COLLATE LATIN_NOCASE)"
                  .ExecuteNonQuery()
              End With
          End Using
  
          Using nCmd As SQLite.SQLiteCommand = c.CreateCommand
              With nCmd
                  .CommandText = "INSERT INTO foo (word) VALUES ('außer')"
                  .ExecuteNonQuery()
              End With
          End Using
  
          Using nCmdSel As SQLite.SQLiteCommand = c.CreateCommand
              With nCmdSel
                  .CommandText = "SELECT * FROM foo WHERE word LIKE '%ss%' COLLATE LATIN_NOCASE"
  
                  Using r As SQLite.SQLiteDataReader = .ExecuteReader
                      Do While r.Read
                          MsgBox(r("word"))
                      Loop
                  End Using
              End With
          End Using
  
      End Sub
  End Class
  </code></pre>