stuart.hemming (5/9/2013)

---

Hello,

I'm hoping someone can help me with a SQL query I'm trying to write; I'm relatively new to SQL so please bear with me if this is a numpty question. I'm using SQL 2012 Developer Edition.

I have a database of address data (for those of you who work in the UK public sector you may be familiar with it as it's from the LLPG) in which there are three fields that can describe the house number or name as follows:

House_No_Start: where a property only has one house number it is stored in this field, e.g. the '1' of the address 1 High Street.

House_No_End: where a property has more that one house number the secondary value is stored in this field, e.g. the '3' of the address 1 - 3 High Street.

House_Name: where a property has a name instead of a house number the value is stored in this field, e.g. 'The manor' of the address The Manor, High Street.

The street name, town, post code, etc. are all stored in other fields across other tables. I can successfully use a select statement and joins to pull together all the elements of the address, but I'm stuck when it comes to selecting only the required fields from the three listed above.

What I want to do is write some kind of IF (or should that be CASE?) statement as part of my wider select statement that pulls together the full address. I want the IF statement to do the following:

Where only the House_No_Start field is populated I want to use the House_No_Start in the address.

Where both the House_No_Start and House_No_End fields are populated I want to concatenate the two values with ' - ' between the two values.

Where only the House_Name field is populated I want to use the House_Name value in the address.

Can this be done? I hope that all makes sense.

Stuart