By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,221 Members | 1,663 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,221 IT Pros & Developers. It's quick & easy.

coalesce and nvl question

P: n/a
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
AS '
declare
fnRetTrue alias for $1;
fnRetFalse alias for $2;
begin
if fnRetTrue = '' or fnRetTrue is not null then
return fnRetTrue;
else
return fnRetFalse;
end if;
end;
'
LANGUAGE plpgsql;

Thanks

Simon

--
Simon Windsor
Email: si***********@cornfield.org.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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2

P: n/a
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: si***********@cornfield.org.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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3

P: n/a
Simon Windsor <si***********@cornfield.org.uk> writes:
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.


I don't think so, but you could use a CASE clause for this.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #4

P: n/a
On Wed, Jun 23, 2004 at 19:10:05 +0100,
Simon Windsor <si***********@cornfield.org.uk> wrote:
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.


Something like the following may suit your purposes:
coallesce(nullif(inputstring,''), 'replacement value')

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #5

P: n/a
Hi

Thanks, I should have thought of that myself!

Simon
On Wed, 2004-06-23 at 19:43, Bruno Wolff III wrote:
On Wed, Jun 23, 2004 at 19:10:05 +0100,
Simon Windsor <si***********@cornfield.org.uk> wrote:
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.


Something like the following may suit your purposes:
coallesce(nullif(inputstring,''), 'replacement value')

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Simon Windsor
Email: si***********@cornfield.org.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.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.