Re: coalesce and nvl question

From: Simon Windsor <simon(dot)windsor(at)cornfield(dot)org(dot)uk>
To: Mike Mascari <mascarm(at)mascari(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: coalesce and nvl question
Date: 2004-06-23 18:10:05
Message-ID: 1088014205.4211.52.camel@celt.cornfield.org.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

I understand that null and '' are different, and MySQL and Oracle
functions are confusing, but my question was not about replacing NULL
but replacing Empty strings. These are handled in MySQL/Oracle by the
same functions that do NULL checks.

Is there a standard function in Postgres that replaces Empty strings, as
against a NULL value.

Simon

On Wed, 2004-06-23 at 18:05, Mike Mascari wrote:
> 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
--
Simon Windsor
Email: simon(dot)windsor(at)cornfield(dot)org(dot)uk
Tel: 01454 617689
Mob: 07960 321599

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
Mailscanner thanks transtec Computers for their support.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Doug McNaught 2004-06-23 18:35:05 Re: coalesce and nvl question
Previous Message Mike Mascari 2004-06-23 17:05:14 Re: coalesce and nvl question