prize18-8  Exercise 3: A Folded Search Column
=============================================

Plan: store fold(title) next to the title (NFD, drop combining marks, casefold) and run LIKE against the folded column with a folded search term.
Plain LIKE ignores case only for ASCII and does not know about accents, so it will fail wherever the user's spelling differs in accents or in non-ASCII case.

ex3.py:

  import sqlite3
  import unicodedata as ud

  def fold(s):
      d = ud.normalize("NFD", s)
      return "".join(c for c in d if not ud.combining(c)).casefold()

  con = sqlite3.connect(":memory:")
  con.execute("CREATE TABLE t (title TEXT, search TEXT)")
  titles = ["\u00c9cole de Paris", "Ecole du soir", "Cr\u00e8me br\u00fbl\u00e9e", "Creme fraiche", "Zo\u00eb", "stra\u00dfe"]
  con.executemany("INSERT INTO t VALUES (?, ?)", [(x, fold(x)) for x in titles])

  def plain(term):
      return [r[0] for r in con.execute("SELECT title FROM t WHERE title LIKE ?", (f"%{term}%",))]

  def folded(term):
      return [r[0] for r in con.execute("SELECT title FROM t WHERE search LIKE ?", (f"%{fold(term)}%",))]

  for term in ["ecole", "\u00e9cole", "creme", "ZOE", "strasse"]:
      print(f"{term:8} plain={plain(term)}  folded={folded(term)}")

Real output (Python 3.14, bundled SQLite):

  ecole    plain=['Ecole du soir']  folded=['École de Paris', 'Ecole du soir']
  école    plain=[]  folded=['École de Paris', 'Ecole du soir']
  creme    plain=['Creme fraiche']  folded=['Crème brûlée', 'Creme fraiche']
  ZOE      plain=[]  folded=['Zoë']
  strasse  plain=[]  folded=['straße']

Where plain LIKE fails: "ecole" misses "École" because the accent differs; "école" (lower-case é) misses "École" because LIKE does not case-fold É to é;
"creme" misses "Crème"; "ZOE" misses "Zoë" for both the case and the accent; "strasse" misses "straße" because ß is not "ss" to LIKE.
The folded column finds all of them because both sides were reduced to the same plain, lower-case form first.