Re: coalesce function

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.