PL/SQL (MOSC)

MOSC Banner

Dealing with NULL values during a concatenate

edited Dec 15, 2011 6:31AM in PL/SQL (MOSC) 5 commentsAnswered
Hi,

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center