Address empty fields with SQL

Databases
#1

I have appended a new field to a database:

db.SQLExecute ("ALTER TABLE weights ADD COLUMN newfield Varchar")

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.

#2

Try:

WHERE newfield IS NULL OR newfield <> 'True'
#3

Different database engines have different null-related functions but Coalesce should be pretty widespread.

SELECT * FROM weights WHERE coalesce(newfield, 'False') <> 'True'
#4

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.

#5

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

#6

Or just put a value in the fields.

ALTER TABLE weights ADD COLUMN newfield Varchar
UPDATE weights SET newfield = ‘False’

#8

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.

#9