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.
