# Address empty fields with SQL

**URL:** <https://forum.xojo.com/t/address-empty-fields-with-sql/33961>\
**Category:** Databases\
**Created:** [October 17, 2016, 1:15am UTC](https://forum.xojo.com/t/address-empty-fields-with-sql/33961 "2016-10-17T01:15:18Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Craig\_Grech](https://forum.xojo.com/user_avatar/forum.xojo.com/craig_grech/32/1499_2.png) [@Craig\_Grech](https://forum.xojo.com/u/Craig_Grech)\
**Post date:** [October 17, 2016, 1:15am UTC](https://forum.xojo.com/t/address-empty-fields-with-sql/33961/1 "2016-10-17T01:15:18Z")

</div>

I have appended a new field to a database:

```auto
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.

```auto
SELECT * FROM weights WHERE newfield <> 'True'
```

Trouble is it doesn’t recognise the empty ones as not True. Only returns the ‘False’ ones.

---

<div class="post-metadata">

**Author:** ![Paul\_Lefebvre](https://forum.xojo.com/user_avatar/forum.xojo.com/paul_lefebvre/32/3152_2.png) [@Paul\_Lefebvre](https://forum.xojo.com/u/Paul_Lefebvre)\
**Post date:** [October 17, 2016, 1:23am UTC](https://forum.xojo.com/t/address-empty-fields-with-sql/33961/2 "2016-10-17T01:23:24Z")

</div>

Try:

```auto
WHERE newfield IS NULL OR newfield <> 'True'
```

---

<div class="post-metadata">

**Author:** ![Scott\_Griffitts](https://forum.xojo.com/letter_avatar_proxy/v4/letter/s/43a26b/32.png) [@Scott\_Griffitts](https://forum.xojo.com/u/Scott_Griffitts)\
**Post date:** [October 17, 2016, 1:23am UTC](https://forum.xojo.com/t/address-empty-fields-with-sql/33961/3 "2016-10-17T01:23:52Z")

</div>

Different database engines have different null-related functions but [Coalesce](http://docs.faircom.com/doc/sqlref/33405.htm) should be pretty widespread.

```auto
SELECT * FROM weights WHERE coalesce(newfield, 'False') <> 'True'
```

---

<div class="post-metadata">

**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\
**Post date:** [October 17, 2016, 6:51am UTC](https://forum.xojo.com/t/address-empty-fields-with-sql/33961/4 "2016-10-17T06:51:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Antonio\_Rinaldi](https://forum.xojo.com/user_avatar/forum.xojo.com/antonio_rinaldi/32/331_2.png) [@Antonio\_Rinaldi](https://forum.xojo.com/u/Antonio_Rinaldi)\
**Post date:** [October 20, 2016, 7:07am UTC](https://forum.xojo.com/t/address-empty-fields-with-sql/33961/5 "2016-10-20T07:07:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Tim\_Hare](https://forum.xojo.com/user_avatar/forum.xojo.com/tim_hare/32/176_2.png) [@Tim\_Hare](https://forum.xojo.com/u/Tim_Hare)\
**Post date:** [October 20, 2016, 7:44am UTC](https://forum.xojo.com/t/address-empty-fields-with-sql/33961/6 "2016-10-20T07:44:48Z")

</div>

Or just put a value in the fields.

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

---

<div class="post-metadata">

**Author:** ![Jym\_Morton](https://forum.xojo.com/letter_avatar_proxy/v4/letter/j/ecd19e/32.png) [@Jym\_Morton](https://forum.xojo.com/u/Jym_Morton)\
**Post date:** [October 20, 2016, 1:20pm UTC](https://forum.xojo.com/t/address-empty-fields-with-sql/33961/8 "2016-10-20T13:20:43Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\
**Post date:** [October 29, 2020, 6:30pm UTC](https://forum.xojo.com/t/address-empty-fields-with-sql/33961/9 "2020-10-29T18:30:43Z")

</div>