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
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.