Best way to check for empty or null value

Question

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

User · Answer

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.

User · Answer

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

User · Answer

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    quot Peters quot      quot jk1 quot   quot jv1 quot     quot jk1 quot   quot jv2 quot    2    quot Russel quot   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

User · Answer

The expression stringexpression      yields  TRUE       for     or for any string consisting of only spaces with the data type char n   NULL       for NULL FALSE     for anything else So to check for   quot stringexpression is either NULL or empty quot    stringexpression       IS NOT FALSE  Or the reverse approach  may be easier to read    stringexpression  lt  gt      IS NOT TRUE  Works for any character type including char n   The manual about comparison operators  Or use your original expression without trim    which is costly noise for char n   see below   or incorrect for other character types  strings consisting of only spaces would pass as empty string  coalesce stringexpression            But the expressions at the top are faster  Asserting the opposite is even simpler   quot stringexpression is neither NULL nor empty quot   stringexpression  lt  gt      About char n  This is about the data type char n   short for  character n    char   character are short for char 1    character 1    Its use is discouraged in Postgres   In most situations text or character varying should be used instead   Do not confuse char n  with other  useful  character types varchar n   varchar  text or  quot char quot   with double-quotes   In char n  an empty string is not different from any other string consisting of only spaces  All of these are folded to n spaces in char n  per definition of the type  It follows logically that the above expressions work for char n  as well - just as much as these  which wouldn t work for other character types   coalesce stringexpression               coalesce stringexpression                   Demo Empty string equals any string of spaces when cast to char n   SELECT     char 5        char 5      AS eq1            char 5          char 5    AS eq2            char 5            char 5  AS eq3   Result   eq1   eq2   eq3  ---- ----- ----  t     t     t  Test for  quot null or empty string quot  with char n   SELECT stringexpression         stringexpression                        AS base test         stringexpression        IS NOT FALSE   AS test1         stringexpression  lt  gt      IS NOT TRUE    AS test2        coalesce stringexpression               AS coalesce1        coalesce stringexpression               AS coalesce2        coalesce stringexpression               AS coalesce3 FROM       VALUES        foo   char 5                                         -- not different from    in char n        NULL       sub stringexpression    Result    stringexpression   base test   test1   test2   coalesce1   coalesce2   coalesce3  ------------------ ----------- ------- ------- ----------- ----------- -----------  foo                f           f       f       f           f           f                     t           t       t       t           t           t                     t           t       t       t           t           t  null               null        t       t       t           t           t  Test for  quot null or empty string quot  with text  SELECT stringexpression         stringexpression                        AS base test         stringexpression        IS NOT FALSE   AS test1         stringexpression  lt  gt      IS NOT TRUE    AS test2        coalesce stringexpression               AS coalesce1        coalesce stringexpression               AS coalesce2        coalesce stringexpression               AS coalesce3 FROM       VALUES        foo   text                                        -- different from    in a sane character types       NULL       sub stringexpression    Result    stringexpression   base test   test1   test2   coalesce1   coalesce2   coalesce3  ------------------ ----------- ------- ------- ----------- ----------- -----------  foo                f           f       f       f           f           f                     t           t       t       t           f           f                     f           f       f       f           f           f  null               null        t       t       t           t           f  db lt  gt fiddle here Old sqlfiddle Related   Any downsides of using data type  quot text quot  for storing strings

User · Answer

Something that I saw people using is stringexpression  gt      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

User · Answer

Checking for the length of the string also works and is compact   where length stringexpression   gt  0

User · Answer

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

User · Answer

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

User · Answer

To check for null and empty   coalesce string             To check for null  empty and spaces  trim the string   coalesce TRIM string

User · Answer

another way is  nullif trim stringExpression      is not null

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

Examples related to sql

Examples related to database

Examples related to postgresql

Examples related to null

Examples related to coalesce