Re: coalesce and nvl question

Simon Windsor wrote:
> Hi
>
> Is there a standard postgres method of replacing empty strings.
>
> In Oracle, nvl handles nulls and empty strings, as does ifnull() in
> MySQL, but with postgres coalesce only handles null strings.
>
> If, not is the best solution to create a plpgsql function, ie
>
> CREATE FUNCTION isEmpty (character varying, character varying) RETURNS
> character varying

This all depends upon what you mean by handle. Do you want to treat
empty strings as NULL or NULL as empty strings? As you said, you can
treat NULL as empty strings using COALESCE:

SELECT COALESCE(x, '');

You can treat empty strings as NULL

SELECT NULLIF(x, '');

But I'd guess most on this list are wondering why you want to equate
an empty string with NULL, as they have two distinct meanings.
Oracle's treatment of empty strings as NULL is world-renowned for
being insane...

HTH

Mike Mascari