Case expression to do an If Else statemen on text? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Case expression to do an If Else statemen on text?

Author

Topic

fuzzyip
Starting Member

35 Posts

Posted - 2008-07-30 : 22:05:26

I have a column that has the name of hotels in it called Hotelname. I want to create a calculated field next to it called Hotelchain that states the company that owns that hotel

For example if I had the following in the Hotelname column

Hotelname
Residence Inn
J W Las Vegas Resort
Las Vegas Marriott Suites
The London --owned by Marriott
Courtyard by Marriott Las Vegas Convention Center
Super 8 - Las Vegas Strip Area

I want the Hotelchain column to state

HotelChain
Residence inn Limited
J Wynn Resorts
Marriott
Marriott
Marriott
Super 8 Company

My idea is to do a case statement with the list of every hotelname and its corresponding hotelchain.

For example I would do something like,

if hotelname like "marriott" then hotelchain = "marriott", else if hotelname like "the london" then hotelchain = "marriott", else if hotelname like "super 8" then hotelchain = "super 8 company", else if hotelname like "residence inn" then hotelchain = "residence inn limited", else if hotelname like "j w" then hotelchain = "J Wynn Resorts"

I would do the above by doing this

SELECT Hotelname,
Hotelchain = CASE hotelname
WHEN hotelname = "marriott" or "The London" THEN 'Marriott'
WHEN hotelname = "Super 8" THEN 'Super 8 Company'
ELSE 'Unknown'
END,
FROM Hotels

Would this work?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-30 : 22:18:40

This should work

SELECT 	Hotelname,
	Hotelchain = 	CASE hotelname
			WHEN hotelname LIKE "%marriott%" or "The London" THEN 'Marriott'
			WHEN hotelname LIKE "%Super 8%" THEN 'Super 8 Company'
			ELSE 'Unknown'
			END
FROM 	Hotels

KH
[spoiler]Time is always against us[/spoiler]

fuzzyip
Starting Member

35 Posts

Posted - 2008-07-31 : 11:49:09

quote:

Originally posted by khtan

This should work

SELECT 	Hotelname,
	Hotelchain = 	CASE hotelname
			WHEN hotelname LIKE "%marriott%" or "The London" THEN 'Marriott'
			WHEN hotelname LIKE "%Super 8%" THEN 'Super 8 Company'
			ELSE 'Unknown'
			END
FROM 	Hotels

KH
[spoiler]Time is always against us[/spoiler]

just ran this, got a

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'LIKE'.

it looks correct to me too, anyone see the error?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 12:51:07

[code]SELECT Hotelname,
Hotelchain = CASE hotelname
WHEN hotelname LIKE '%marriott%' or hotelname LIKE 'The London' THEN 'Marriott'
WHEN hotelname LIKE '%Super 8%' THEN 'Super 8 Company'
ELSE 'Unknown'
END
FROM Hotels[/code]

use single quotes

fuzzyip
Starting Member

35 Posts

Posted - 2008-07-31 : 13:00:37

quote:

Originally posted by visakh16

SELECT 	Hotelname,
	Hotelchain = 	CASE hotelname
			WHEN hotelname LIKE '%marriott%' or hotelname LIKE 'The London' THEN 'Marriott'
			WHEN hotelname LIKE '%Super 8%' THEN 'Super 8 Company'
			ELSE 'Unknown'
			END
FROM 	Hotels

use single quotes

Got it, it was a combination of the single quotes and the "hotelname" right after the case that got it, should be case when, not case hotelname when

Thanks guys

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 13:04:44

quote:
Originally posted by fuzzyip

quote:
Originally posted by visakh16

SELECT Hotelname,
Hotelchain = CASE ~~hotelname~~
WHEN hotelname LIKE '%marriott%' or hotelname LIKE 'The London' THEN 'Marriott'
WHEN hotelname LIKE '%Super 8%' THEN 'Super 8 Company'
ELSE 'Unknown'
END
FROM Hotels

use single quotes

Got it, it was a combination of the single quotes and the "hotelname" right after the case that got it, should be case when, not case hotelname when

Thanks guys

just noticed, you need to remove column name near CASE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-01 : 06:06:01

quote:
Originally posted by visakh16

use single quotes

My eyesight is failling

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources