Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do I use an IF type function within a SELECT statement? Expand / Collapse
Author
Message
Posted Thursday, May 9, 2013 9:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 2:46 AM
Points: 2, Visits: 10
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
Post #1451213
Posted Thursday, May 9, 2013 9:54 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 4,352, Visits: 6,168
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 at GMail
Post #1451224
Posted Friday, May 10, 2013 4:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 5,047, Visits: 11,803
You could try something like this (untested):

Select HouseLine1 = (case when len(House_No_End) > 0 then House_No_Start + ' - ' + House_No_End 
when len(House_No_Start) > 0 then House_No_Start
else House_Name end)




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1451500
Posted Tuesday, May 21, 2013 5:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:09 AM
Points: 883, Visits: 2,807
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;





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1454927
Posted Tuesday, May 21, 2013 1:29 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 1,006, Visits: 3,031
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 name
SELECT 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

Post #1455192
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse