On 5 Jul 2018, at 6:22am, Andy Goth <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
