For existing records before the change this field will be empty unless the record is edited later on. Then it will contain a string ‘True’ or ‘False’.
(For compatibility with an older database I don’t want to use a boolean).
Now I want to select all the records that aren’t True, that is the ones that are False and the ones that never contained anything.
SELECT * FROM weights WHERE newfield <> 'True'
Trouble is it doesn’t recognise the empty ones as not True. Only returns the ‘False’ ones.
for readibility you might want to look into IFNULL … this one however DOES have various syntax depending on the DB Engine
where as Scott said. coalesce is more widespread.
if you have and empty field and not a NULL field (empty is different from NULL) you could use NULLIF to standardize your field:
where IFNULL( NULLIF(field, ‘’), ‘False’) <> ‘True’
If your field is NULL then NULLIF will return NULL
if your field is EMPTY (IE ‘’) then NULL IF will return NULL
otherwise NULLIF will return the field value
IFNULL will return the first argument if it is not NULL otherwise the second argument
COALESCE is an equivalent form but if available IFNULL (or ISNULL in MSSQL) is more efficient
Why would there be a compatibility issue with another database? Using a Variant as should be enough when reading or writing to the field as either Boolean or Varchar.