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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to