Delete rows in which the cell of a specific column is empty

Question

Is there a way to delete all rows in QGIS in which the cell of a specific column in the attribute table is empty? e.g. line three in the table below. I need to do this for a large data set with about 180,000 rows.

| ID  | 2LE  | CHECK |
|-----|------|-------|
| 1   | PT   | FALSE |
| 2   | PT   | TRUE  |
| 3   | NULL | TRUE  |
| 4   | AT   | FALSE |

Answer 1

You could use the following in the Python Console. Select your layer and use something like:

layer = iface.activeLayer()
with edit(layer):
    listOfIds = [feat.id() for feat in layer.getFeatures() if feat['fieldName'] == NULL]
    layer.deleteFeatures(listOfIds)

Answer 2

You could also filter your layer.

Right click on the layer choose Filter and use "2LE" IS NOT NULL. Afterwards you can right click and Save As another vector layer.

Answer 3

You could use a Virtual Layer, to keep the original data set and to create a new one.

If there is no geometry you have to check "No geometry" in the "Create a virtual layer" window.

There are different ways to check for NULL or ``. See also SQLite select where empty? - on Stack Overflow

Example queries. I was curious about and tested them all with success (no errors):

SELECT * FROM your_table WHERE "2LE" IS NOT NULL

>

SELECT * FROM your_table WHERE "2LE" != 'NULL'  #if `NULL` is a string:

>

SELECT * FROM your_table WHERE ifnull("2LE", '') != ''

>

SELECT * FROM your_table WHERE ifnull(length("2LE"), 0) != 0

>

SELECT * FROM your_table WHERE coalesce("2LE", '') != ''

You can Save As (right click) the query (virtual layer) to a new vector file.

Answer 4

In the attribute table, use "Select by Expression" (not Filter) and input the expression to match the features you want deleted - "2LE" IS NULL would seem to do it. Then you should get all those features selected, and they should appear in yellow.

Then with editing turned on (toggled by the pencil icon in the toolbar or elsewhere) hit Ctrl-X or use "Cut Features" in the "Edit" menu. The selected features should disappear.

You can then toggle the editing off (pencil icon) and will be prompted to save the changes if you want, which will overwrite the original data on disk or database.