eww.sql

SELECT *
FROM Entries
-- Find all rows where headword, headword_abbreviation or alternate_headword contains the search term
WHERE CASE
    WHEN CASE WHEN headword LIKE "cell%" THEN 1 ELSE INSTR(LOWER(headword), LOWER(" cell")) END = 0
    THEN 1000
    ELSE INSTR(SUBSTR(headword || " ", CASE WHEN headword LIKE "cell%" THEN 1 ELSE INSTR(LOWER(headword), LOWER(" cell")) END + 4), " ")
    END != 1000 OR CASE
    WHEN CASE WHEN headword_abbreviation LIKE "cell%" THEN 1 ELSE INSTR(LOWER(headword_abbreviation), LOWER(" cell")) END = 0
    THEN 1000
    ELSE INSTR(SUBSTR(headword_abbreviation || " ", CASE WHEN headword_abbreviation LIKE "cell%" THEN 1 ELSE INSTR(LOWER(headword_abbreviation), LOWER(" cell")) END + 4), " ")
    END != 1000 OR CASE
    WHEN CASE WHEN alternate_headword LIKE "cell%" THEN 1 ELSE INSTR(LOWER(alternate_headword), LOWER(" cell")) END = 0
    THEN 1000
    ELSE INSTR(SUBSTR(alternate_headword || " ", CASE WHEN alternate_headword LIKE "cell%" THEN 1 ELSE INSTR(LOWER(alternate_headword), LOWER(" cell")) END + 4), " ")
    END != 1000 AND url_encoded_headword > ""
-- Sort by how close a match the search term is (defined as how close to a full match the matching word is)
-- Tie break by the headword, alphabetically
ORDER BY CASE
    WHEN CASE WHEN headword LIKE "cell%" THEN 1 ELSE INSTR(LOWER(headword), LOWER(" cell")) END = 0
    THEN 1000
    ELSE INSTR(SUBSTR(headword || " ", CASE WHEN headword LIKE "cell%" THEN 1 ELSE INSTR(LOWER(headword), LOWER(" cell")) END + 4), " ")
    END, CASE
    WHEN CASE WHEN headword_abbreviation LIKE "cell%" THEN 1 ELSE INSTR(LOWER(headword_abbreviation), LOWER(" cell")) END = 0
    THEN 1000
    ELSE INSTR(SUBSTR(headword_abbreviation || " ", CASE WHEN headword_abbreviation LIKE "cell%" THEN 1 ELSE INSTR(LOWER(headword_abbreviation), LOWER(" cell")) END + 4), " ")
    END, CASE
    WHEN CASE WHEN alternate_headword LIKE "cell%" THEN 1 ELSE INSTR(LOWER(alternate_headword), LOWER(" cell")) END = 0
    THEN 1000
    ELSE INSTR(SUBSTR(alternate_headword || " ", CASE WHEN alternate_headword LIKE "cell%" THEN 1 ELSE INSTR(LOWER(alternate_headword), LOWER(" cell")) END + 4), " ")
    END, headword