How to concatenate addresses in one declared variable?

Question

I have current code that builds the string of the addresses like this:

    addressStreet = "";
    if(q1.addr1 != "") {
        addressStreet = addressStreet & q1.addr1;
    }

    if(q1.addr2 != "") {
        addressStreet = addressStreet & "<br>" & q1.addr2;
    }

    if(q1.addr3 != "") {
        addressStreet = addressStreet & "<br>" & q1.addr3;
    }

    if(q1.addr4 != "") {
        addressStreet = addressStreet & "<br>" & q1.addr4;
    }

Q1 is query where all columns are located. I was trying to do all of this concatenating in Sybase SQL query. Here is example of what I have tried:

DECLARE @AddressStreet VARCHAR(500)
SELECT 
    CASE 
        WHEN datalength(address1) > 0 THEN @AddressStreet + address1  
        WHEN datalength(address2) > 0 THEN @AddressStreet + address2
        WHEN datalength(address3) > 0 THEN @AddressStreet + address3
        WHEN datalength(address4) > 0 THEN @AddressStreet + address4
    END AS AddressStr
FROM Info
WHERE recid = 1214

Query above produced this result:

18 Wolf Street,

That result is incorrect because address2 column has this value: Suite 408. The address should look like this:

18 Wolf Street,
Suite 408

Is there a way to get desired output in Sybase SQL? Thank you.

Answer 1

When you have a CASE statement, the first condition that is met is executed. So in your case, length of address1 is greater than 0 so @AddressStreet + address1 gets executed, and nothing else. I don't know Sybase well enough to know what functions are available, but you can surely try something like:

SELECT CASE WHEN datalength(address1) > 0 THEN address1 ELSE '' END +
       CASE WHEN datalength(address2) > 0 THEN address2 ELSE '' END +
       CASE WHEN datalength(address3) > 0 THEN address3 ELSE '' END +
       CASE WHEN datalength(address4) > 0 THEN address4 ELSE '' END
       AS AddressStr
  FROM Info
 WHERE recid = 1214

This won't have any whitespace between the address fields, so you might want to have something something like '<BR>' + address2, etc. if you want it to produce the HTML line breaks.