Need to join two strings if both contain values, or return one value if first is NULL

enter code hereIn my SQL Server database I have my address information for the subNumber (e.g. Unit 802) and the streetNumber (e.g. 242 Elizabeth Street) stored separately.

I need to display these as one (i.e. 802/242 Elizabeth Street) if the subNumber contains a value, otherwise just return the streetNumber if it does not.

I've been working toward a solution using IF ELSE and a foreach loop after accessing the data through LINQ - but I'm stuck after the point where I have completed the loop. I would also be happy to do this with a SELECT Stored Procedure in SQL - open to suggestions!

DataClassesDataContext dc = new DataClassesDataContext();

var recent = from p in dc.Properties
             orderby p.modtime descending
             where p.status == "Current"
             select new
             {
                 rsub = (p.subNumber).ToString(),
                 rnumber = (p.streetNumber).ToString(),
                 rstreet = p.street,
                 rsuburb = p.suburb,
                 rurl = p.propertyURL,
             };

foreach (var home in recent)
{
    if (string.IsNullOrEmpty(home.rsub))
    {
        string rnum = home.rnum开发者_JS百科ber;
    }
    else
    {
        string rnum = home.rsub + "/" + home.rnumber;
    }
}

recentrepeater.DataSource = recent;
recentrepeater.DataBind();

Yahia gave the best option in c# - this is the SQL solution I have finally ended up with:

ALTER PROCEDURE GetPropertyShort

AS
SELECT TOP 5 ISNULL(convert(varchar(5), subNumber) + '/' + convert(varchar(5), streetNumber), convert(varchar(5), streetNumber)) as Number, street, suburb, propertyURL, modtime
FROM Property
ORDER BY modtime DESC

try

var recent = from p in dc.Properties
        orderby p.modtime descending
        where p.status == "Current"
        select new
       {
            rsub = (p.subNumber).ToString(),
            rnumber = (p.streetNumber).ToString(),
            rnum = string.IsNullOrEmpty((p.subNumber).ToString()) ? (p.streetNumber).ToString() : (p.subNumber).ToString() + "/" + (p.streetNumber).ToString(),
            rstreet = p.street,
            rsuburb = p.suburb,
            rurl = p.propertyURL,
        };

Just a ternary conditional aka ?: operator in the "select" should do:

select new
{
    house = p.subNumber != null
        ? p.subNumber + "/" + p.streetNumber
        : p.streetNumber;
    ...
};

This makes the assumption street number is always there (or it might result in "xxx/" or null). It also assumes that sub is null (not just empty) if truly not present.

If it starts to get "too complicated", consider the following (which has slightly different rules than above, those are left to be figured out):

select new
{
    house = PrettyHouseNumber(p.subNumber, p.streetNumber),
    ...
};

string PrettyHouseNumber(string sub, string street) {
    // ?: could also be used here as well, but since invoking the method
    // can be used as an expression itself, breaking it up like this also
    // allows the use of other constructs
    if (!string.IsNullOrEmpty(sub)) {
        return sub + "/" + street;
    } else {
        return "" + street; // NULL will go to "", if it can even ever come up
    }
}

Which should show how any expression, including a method call, can be used there -- pass it some data and get some data back :) While there are limits with expression trees and which ones can be efficiently turned into SQL, since this is just processing data already returned then there is nothing to worry about here.

Happy coding.

On the table level you could create a computed column; these are not stored - the value is 'created' when the column is queried.

CREATE TABLE [Customer]
(
     [subNumber] NVARCHAR(256),
     [streetNumber] NVARCHAR(256),
     [fullAddress] AS (CASE 
                      WHEN [subNumber] IS NULL THEN [streetNumber]
                      ELSE [subNumber] + N' ' + [streetNumber]
                    END)
);
GO

Or you can add it the table:

ALTER TABLE [Customer]
   ADD COLUMN [fullAddress]
       AS (CASE 
            WHEN [subNumber] IS NULL THEN streetNumber
            ELSE [subNumber] + N' ' + streetNumber
          END);
GO

Now the value will be directly-accessible from your EF model.