Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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"
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;
Can you elaborate your question a little please with some expected output you want to see?
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
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;