[sql] Best way to check for "empty or null value"

What is best way to check if value is null or empty string in Postgres sql statements?

Value can be long expression so it is preferable that it is written only once in check.

Currently I'm using:

coalesce( trim(stringexpression),'')=''

But it looks a bit ugly.

stringexpression may be char(n) column or expression containing char(n) columns with trailing spaces.

What is best way?

another way is

nullif(trim(stringExpression),'') is not null

To check for null and empty:

coalesce(string, '') = ''

To check for null, empty and spaces (trim the string)

coalesce(TRIM(string), '') = ''

Something that I saw people using is stringexpression > ''. This may be not the fastest one, but happens to be one of the shortest.

Tried it on MS SQL as well as on PostgreSQL.

In ran into a kind of similar case, were I had to do this . My Table definition look like :

id(bigint)|name (character varying)|results(character varying)
1 | "Peters"| [{"jk1":"jv1"},{"jk1":"jv2"}]
2 | "Russel"| null

To filter out the results column with null or empty in it , what worked was :

SELECT * FROM tablename where results NOT IN  ('null','{}'); 

This returned all rows which are not null on results.

I'm not sure how to fix this query to return the same all rows which are not null on results.

SELECT * FROM tablename where results is not null; 

--- hmm what am I missing,casting ? any inputs?

If database having large number of records then null check can take more time you can use null check in different ways like : 1) where columnname is null 2) where not exists() 3) WHERE (case when columnname is null then true end)

My preffered way to compare nullable fields is: NULLIF(nullablefield, :ParameterValue) IS NULL AND NULLIF(:ParameterValue, nullablefield) IS NULL . This is cumbersome but is of universal use while Coalesce is impossible in some cases.

The second and inverse use of NULLIF is because "NULLIF(nullablefield, :ParameterValue) IS NULL" will always return "true" if the first parameter is null.

If there may be empty trailing spaces, probably there isn't better solution. COALESCE is just for problems like yours.

A lot of the answers are the shortest way, not the necessarily the best way if the column has lots of nulls. Breaking the checks up allows the optimizer to evaluate the check faster as it doesn't have to do work on the other condition.

(stringexpression IS NOT NULL AND trim(stringexpression) != '')

The string comparison doesn't need to be evaluated since the first condition is false.

Checking for the length of the string also works and is compact:

where length(stringexpression) > 0;