How to query and space address with multiple values when some are NULL-CodePudding

I'm trying to query and correctly format an address made up of multiple values in Oracle when some of those values are NULL. Coalesce() works well for this but not when I add spacing/punctuation.

Examples

address 1: 123 Main St
address 2: Apt 1
City: New York
State: NY
Postal Code: 10001
Country: USA

address 1: NULL
address 2: NULL
City: New York
State: NULL
Postal Code: 10001
Country: USA

When pulling in the full address, I'm wanting to ignore the subsequent punctuation if a value is NULL so there aren't excess commas/spaces.

select a.address1 || ' ' || a.address2 || ', ' || a.city || ', ' || a.state || ' ' || a.postal_code || ', ' || 'USA',
Coalesce(a.address1, a.address2, a.city, a.state, a.postal_code,'USA') 
from address a

Example 1 Result: 123 Main St Apt 1, New York, NY 10001, USA
Example 2 Result: , New York, 10001, USA

Desired Result for example 2: New York, 10001, USA

This is just one example but I'm wanting a still properly formatted line when any combination of the elements are missing.

CodePudding user response：

You could us the nvl2() function to append the comma and space to each column value, only if it is not null, and then just concatenate those together:

select nvl2(address1, address1 || ', ', null)
  || nvl2(address2, address2 || ', ', null)
  || nvl2(city, city || ', ', null)
  || nvl2(state, state || ', ', null)
  || nvl2(postal_code, postal_code || ', ', null)
  || 'USA' as address
from address

ADDRESS
123 Main St, Apt 1, New York, NY, 10001, USA
New York, 10001, USA

fiddle

If you weren't adding the fixed 'USA' at the end then you could end up with a trailing comma, which you could trim off.

... except I just noticed you don't want a comma between address1 and address2... you can handle that with a nested nvl2():

select nvl2(address1, address1 || nvl2(address2, ' ', ', '), null)
  || nvl2(address2, address2 || ', ', null)
  || nvl2(city, city || ', ', null)
  || nvl2(state, state || ', ', null)
  || nvl2(postal_code, postal_code || ', ', null)
  || 'USA' as address
from address

ADDRESS
123 Main St Apt 1, New York, NY, 10001, USA
123 Main St, New York, NY, 10001, USA
Apt 1, New York, NY, 10001, USA
New York, 10001, USA

fiddle (with added rows with just address1 or address2, as well as both/neither, for the output shown above).

CodePudding user response：

Try:

REGEXP_REPLACE('Just concat without worrying about NULL','( ,) ', '')