Sqlite: how to delete rows that contain null/empty strings

Question

First of all thanks for your help. I do an learning SQL, so I need some help.

I have a Sqlite database in which some fields in a certain column contains nothing or string of spaces.

Please How do I delete the rows containing nothing (or string of spaces) from the database?

Thanks for your help.

Graham Borland · Accepted Answer · 2013-05-16 14:22:49Z

41

Try this:

DELETE FROM myTable WHERE myColumn IS NULL OR trim(myColumn) = '';

The trim() is necessary so that strings containing just whitespace are collapsed to an empty string.

edited May 16, 2013 at 14:22

answered May 16, 2013 at 12:47

Graham Borland

60.8k21 gold badges144 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Graham Borland Over a year ago

Could you share your test data? That works fine for me with SQLite.

Lisa Anne Over a year ago

of course. But I have to send you the db via email because I am behind a firewall

Timo Over a year ago

sqlite kungfu plus one, it worked with DELETE FROM myTable WHERE myColumn IS null

KF2 · Accepted Answer · 2013-05-16 12:49:41Z

4

Try this:

DELETE FROM tbl
WHERE
  (filed IS NULL OR filed  = '')

Multiple Column:

DELETE FROM tbl
WHERE
  (filed IS NULL OR filed  = '')
  AND (filed2 IS NULL OR filed2 = '')
  AND (filed3 IS NULL OR filed2 = '')

answered May 16, 2013 at 12:49

KF2

10.2k8 gold badges46 silver badges77 bronze badges

Comments

Bohemian · Accepted Answer · 2013-05-16 13:13:14Z

1

This answer will work for any SQL database:

DELETE FROM MY_TABLE
WHERE MY_COLUMN IS NULL
OR TRIM(MY_COLUMN) = ''

See this WHERE clause working in SQLFiddle

edited May 16, 2013 at 13:13

answered May 16, 2013 at 12:47

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

3 Comments

Graham Borland Over a year ago

This doesn't detect the "string of spaces" case.

Graham Borland Over a year ago

Nope, I've just tested with SQLite. A string of spaces does not match ''.

Bohemian Over a year ago

@GrahamBorland I just tested it too. and was surprised to find it needed the TRIM()... answer edited

Nirav · Accepted Answer · 2015-12-24 06:55:43Z

1

DELETE FROM MY_TABLE WHERE MY_COLUMN IS NULL OR MY_COLUMN like ''

answered Dec 24, 2015 at 6:55

Nirav

113 bronze badges

Collectives™ on Stack Overflow

Sqlite: how to delete rows that contain null/empty strings

4 Answers 4

3 Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related