From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: coalesce function |
Date: | 2013-06-20 21:36:26 |
Message-ID: | 1371764186012-5760205.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
itishree sukla wrote
> Hi All,
>
> I am using coalesce(firstname,lastname), to get the result if first name
> is
> 'NULL' it will give me lastname or either way. I am having data like
> instead of NULL, blank null ( i mean something like '' ) for which
> coalesce is not working, is there any workaround or other function
> available in postgresql, please do let me know.
>
>
> Regards,
> Itishree
This is the solution I am currently using in my work:
Runs in 9.0
CREATE OR REPLACE FUNCTION coalesce_emptystring(VARIADIC in_ordered_actual
varchar[])
RETURNS varchar
AS $$
SELECT input
FROM ( SELECT unnest($1) AS input ) src
WHERE input IS NOT NULL AND input <> ''
LIMIT 1;
$$
LANGUAGE sql
STABLE
;
Same usage syntax as the built-in COALESCE but skips NULL and the
empty-string. Note a string with only whitespace (i.e., ' ') is not
considered empty.
The problem with the "CASE" example provided is that while it works in the
specific case you are solving it does not readily generalize to more than 2
inputs.
Are you positive the "lastname" will always have a value? You should
consider a last-resort default to ensure that the column never returns a
NULL.
coalesce_emptystring(firstname, lastname, 'Name Unknown')
--
View this message in context: http://postgresql.1045698.n5.nabble.com/coalesce-function-tp5760161p5760205.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Jason Long | 2013-06-20 22:03:06 | Re: Problem with left join when moving a column to another table |
Previous Message | Tom Lane | 2013-06-20 19:40:43 | Re: intagg |