Concatenate Address With Blank Fields

[mwscott1]

I am trying to concatenate several address fields from one table into one formatted address. If the field has a NULL value I would like to skip it and go to the next. I have tried + and & nothing seems to work.

SQL SELECT

"ADDRESS_LINE_1",

"ADDRESS_LINE_2",

    "ADDRESS_LINE_3",

    "ADDRESS_LINE_4",

    "ADDRESS_LINE_5",

    "ZIP_POSTAL_CODE"

FROM DBZA.APR;

If there is a way to insert line breaks to somewhat format the address like below.

ADDRESS_LINE_1

ADDRESS_LINE_2

ADDRESS_LINE_3

ADDRESS_LINE_4

ADDRESS_LINE_5 + ZIP_POSTAL_CODE"

[vishsaggi]

Can you elaborate your question a little please with some expected output you want to see?

[mwscott1]

This is an example of the data now

ADDRESS_LINE_1	ADDRESS_LINE_2	ADDRESS_LINE_3	ADDRESS_LINE_4	ADDRESS_LINE_5	ZIP_POSTAL_CODE
3619 SUNSHINE AVENUE				HAPPY TOWN                  NE	95842

I would all the address info concatenated  as a new field ADDRESS and with the NULL values removed.

3619 SUNSHINE AVENUE HAPPY TOWN  NE 95842

or possible formatted with line breaks like this

3619 SUNSHINE AVENUE

HAPPY TOWN                  NE 95842

[vishsaggi · Accepted solution]

Try this may be:

LOAD *, 

     "ADDRESS_LINE_1" & ' ' &  "ADDRESS_LINE_2" & ' ' &  "ADDRESS_LINE_3" & ' ' & "ADDRESS_LINE_4" & ' ' & 

     "ADDRESS_LINE_5" & ' ' &  "ZIP_POSTAL_CODE" AS [FUll Adress];

SQL SELECT

"ADDRESS_LINE_1",

"ADDRESS_LINE_2",

    "ADDRESS_LINE_3",

    "ADDRESS_LINE_4",

    "ADDRESS_LINE_5",

    "ZIP_POSTAL_CODE"

FROM DBZA.APR;