[GENERAL] coalesce and nvl question
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: [EMAIL PROTECTED] 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
Re: [GENERAL] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] coalesce and nvl question
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: [EMAIL PROTECTED] 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
Re: [GENERAL] coalesce and nvl question
Simon Windsor [EMAIL PROTECTED] 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 [EMAIL PROTECTED])
