Dealing with NULL values during a concatenate
I have a materialized view where one of the fields is a concatenation of multiple fields. The field called street_direction may be null in most cases so I don't want an extra space in the end result.
Example:
SELECT street_number || ' ' ||street_name || ' ' || street_suffix || (nvl((' ' || street_direction), null)) || ', ' || city AS location;
Result:
31 MAIN STREET WEST, HAMILTON
or if there is no direction I get stuck with an extra space.
31 MAIN STREET , HAMILTON - How do I get rid of this space?
Thanks,
Gemini