SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How do I use an IF type function within a SELECT statement?


How do I use an IF type function within a SELECT statement?

Author
Message
stuart.hemming
stuart.hemming
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12491 Visits: 8554
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
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18551 Visits: 20441
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1778 Visits: 3432
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
mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1584 Visits: 3317
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search