Select address ignoring blank columns

Question

Ingres DB, property data as below

addr1
addr2
addr3
addr4
postcode

Some address are short and so have no values in addr3 or addr4. E.g.

addr1 : 14 Random Street
addr2 : City
addr3: 
addr4: 
postcode : LT1 5GH

The postcode is always populated.

How can I select the address elements in contiguous fields using SQL?

So the output I want would be as below

14 Random Street, City, LT1 5GH
Flat 5, 15 Random Street, District, London, E35 5FG
67 Whatever Crescent, Lovely Street, Eastwich, LH6 &GH

Not quite sure what you mean, can you provide an example? – Ben Hamilton Oct 31 '16 at 13:52 — Ben Hamilton, Oct 31 '16 at 13:52

score 2 · Accepted Answer · answered Oct 31 '16 at 13:52

You can try this using a case expression.

case when addr1<> '' and addr1 is not null then addr1 +',' else '' end +
case when addr2<> '' and addr2 is not null then addr2 +',' else '' end +
case when addr3<> '' and addr3 is not null then addr3 +',' else '' end +
case when addr4<> '' and addr4 is not null then addr4 +',' else '' end +
zip

Use concat or a similar function if + is not the concatenation operator in ingres.

score 1 · Answer 2 · answered Nov 01 '16 at 06:10

SELECT CONCAT( CASE WHEN ISNULL(Add1,'') <> '' THEN Add1 + ',' END , CASE WHEN ISNULL(Add2,'') <> '' THEN Add2 + ',' END,CASE WHEN ISNULL(Add3,'') <> '' THEN Add3 + ',' END,CASE WHEN ISNULL(Add4,'') <> '' THEN Add4 + ',' END,CASE WHEN ISNULL(postcode,'') <> '' THEN postcode END)

Select address ignoring blank columns

2 Answers2