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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
