How to match not null + not empty?

Question

I have to do some queries on a messy database. Some columns are filled with either null or an empty string. I can do a query like this:

select * from a where b is not null and b <> '';

But is there a shortcut for this case? (match every "not empty" value) Something like:

select * from a where b is filled;

Not a duplicate. The other question is `null or empty` while this question is `not null and not empty` — Clodoaldo Neto, Jan 30 '17 at 15:18

score 53 · Accepted Answer · answered Jan 30 '17 at 15:21

53

Just:

where b <> ''

will do what you want as null <> '' is null and the row will not be returned

answered Jan 30 '17 at 15:21

Clodoaldo Neto

1

or `LTRIM(b) <> ''` if there's the possibility of rows with more than a single space. – Nick Jul 07 '22 at 15:54
`invalid input syntax for type integer: ""` so, if column b is INT then `b <> 0` but your answer is enough correct for the Q: '...or an empty string' – Vasilii Suricov Jul 26 '23 at 13:01

score 3 · Answer 2 · answered Jan 30 '17 at 14:48

3

select * from a where COALESCE(b, '') <> '';

answered Jan 30 '17 at 14:48

Steve Smith

2 Answers2