On 5 Jul 2018, at 6:22am, Andy Goth <andrew.m.g...@gmail.com> wrote: > Or equivalently, "ISNULL" instead of "IS NULL".
There is no ISNULL in sqlite3. Also, the functions ifnull() and nullif() are not useful for actually testing for NULL. Continuing to eliminate options, regular expressions do not match with NULL in a useful manner. As your examples show, you are reduced to variations on CASE WHEN x IS NULL THEN ... ELSE ... END > CASE age > WHEN < 1 THEN 'baby' > WHEN < 3 THEN 'toddler' > WHEN < 5 THEN 'preschooler' > WHEN < 12 THEN 'gradeschooler' > WHEN < 18 THEN 'teenager' > WHEN < 21 THEN 'young adult' > ELSE 'adult' END You can replace this with a table lookup. Create a table like the following CREATE TABLE AgeNames (age INTEGER UNIQUE, name TEXT COLLATE NOCASE); INSERT INTO AgeNames (1, 'baby'),(3,'toddler'),(5 ... then do a SELECT looking for SELECT name FROM AgeNames WHERE ?1 >= age ORDER BY rowid LIMIT 1 You will have to find a value for "age" which deals with the case where you're looking up NULL. It might work just to set "age" to NULL. Similar things can be done with the other CASE statements that match on numbers. Simon. _______________________________________________ sqlite-users mailing list firstname.lastname@example.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users