Thread: Concatenation containing a "null" term
(I am certain I once saw the answer to the following question, but I can't for the life of me find out where!)
I create a View as
SELECT (firstname || ' ' || lastname)::character(50) AS fullname
FROM name_table;
firstname and lastname are text columns, and firstname may be null.
Unfortunately, it seems that whenever firstname is null, the resulting fullname is null also. Is there a way of having the null-strings terms of the concatenation operator be considered as empty strings instead?
Thanks for the help!
I create a View as
SELECT (firstname || ' ' || lastname)::character(50) AS fullname
FROM name_table;
firstname and lastname are text columns, and firstname may be null.
Unfortunately, it seems that whenever firstname is null, the resulting fullname is null also. Is there a way of having the null-strings terms of the concatenation operator be considered as empty strings instead?
Thanks for the help!
On Sun, May 28, 2006 at 05:17:12PM -0400, Pierre Thibaudeau wrote: > Is there a way of having the null-strings terms of the concatenation > operator be considered as empty strings instead? Use COALESCE. Some people create a custom concatenation operator that automatically does so. http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html#AEN12639 http://www.postgresql.org/docs/8.1/interactive/sql-createoperator.html -- Michael Fuhr
> Is there a way of having the null-strings terms of the concatenation
> operator be considered as empty strings instead?
Use COALESCE. Some people create a custom concatenation operator
that automatically does so.
http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html#AEN12639
http://www.postgresql.org/docs/8.1/interactive/sql-createoperator.html
Simple, swift and bang on target! Thank you!