How do I use an IF type function within a SELECT statement?
-
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
-
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
1) this design seems WAY over-normalized to me
2) take a look at the COALESCE statement. That could be what you need if your design is LEFT JOIN based and/or you store NULL values in "missing" columns. CASE would well be part of a solution as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
You could try something like this (untested):
Select HouseLine1 = (case when len(House_No_End) > 0 then House_No_Start + ' - ' + House_No_Endwhen len(House_No_Start) > 0 then House_No_Start
else House_Name end)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
If the columns are nullable then you can concatenate.
CREATE TABLE House(House_No_Start VARCHAR(10) NULL,
House_No_End VARCHAR(10) NULL,
House_Name VARCHAR(50) NULL);
GO
INSERT INTO House VALUES
('1', '3', NULL),
('2', NULL, NULL),
(NULL, NULL, 'The Manor');
GO
SELECT
ISNULL(House_No_Start, '') + ISNULL(' - ' + House_No_End, '') + ISNULL(House_Name, '') AS House
FROM
House;
GO
DROP TABLE House;
-
Sean Pearce (5/21/2013)
If the columns are nullable then you can concatenate....SELECT
ISNULL(House_No_Start, '') + ISNULL(' - ' + House_No_End, '') + ISNULL(House_Name, '') AS House
FROM
House;
...
I frequently use (and prefer) the method that Sean has posted. One caveat, watch out for empty strings rather than nulls.
What is the rule for all fields populated?
Here's a couple of options to handle that situation.
--House Numbers have priority over house nameSELECT COALESCE(
House_No_Start + ISNULL(' - ' + House_No_End, ''),
House_Name + ','
) AS House
--Retain House Name prior to number range
SELECT ISNULL(House_Name,'') + -- House_name or empty
ISNULL(SUBSTRING(', ' + House_Name + House_No_Start,1,2),'') + -- Comma if house_name and house_no_start
ISNULL(House_No_Start, '') + ISNULL(' - ' + House_No_End, '') -- House number range or empty
AS House
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply