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, Commented Jan 30, 2017 at 15:18

Clodoaldo Neto · Accepted Answer · 2017-01-30 15:21:13Z

60

Just:

where b <> ''

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

answered Jan 30, 2017 at 15:21

Clodoaldo Neto

123k28 gold badges243 silver badges268 bronze badges

2

or LTRIM(b) <> '' if there's the possibility of rows with more than a single space.
– Nick
Commented Jul 7, 2022 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
Commented Jul 26, 2023 at 13:01

Add a comment |

Steve Smith · Accepted Answer · 2017-01-30 14:48:42Z

3

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

answered Jan 30, 2017 at 14:48

Steve Smith

2,2302 gold badges18 silver badges22 bronze badges

Add a comment |

2 Answers 2