case ? and like? - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

case ? and like?

Author

Topic

marek
Starting Member

34 Posts

Posted - 2010-01-28 : 03:18:13

Hallo all
I need help please.

tab1

TOWN
chicago
parisMarseil
vancouverToronto
chicagoLasvegas
paris

result:

tab1

TOWN
usa
france
canada
usa
france

these cities (Chicago,Lasvegas,Marseil, Vancouver, Paris and Toronto
) are always constant cities. I have made the following syntax,to supplement town, but it's wrong. Therefore, please help.
Here is my wrong syntax:

select town from tab1
case (like "%paris%" : "france")
case (like "%lasvegas%" : "usa")
case (like "%marseil%" : "france")
case (like "%chicago%" : "usa")
case (like "%vancouver%" : "canada")
case (like "%toronto%" : "canada")

thanks for help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-28 : 03:44:32

is this mapping stored some where?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-01-28 : 03:44:33

SELECT CASE WHEN town LIKE '%chicago%' THEN 'USA'
WHEN town LIKE '%paris%' THEN 'France'
WHEN town LIKE '%lasvegas%' THEN 'usa'
WHEN town LIKE '%marseil%' THEN 'france'
WHEN town LIKE '%vancouver%' THEN 'canada'
WHEN town LIKE '%toronto%' THEN 'canada' END
FROM TABLE

marek
Starting Member

34 Posts

Posted - 2010-01-28 : 03:50:53

No, this mapping is not stored some where....

bklr thanks for help

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 04:16:04

" this mapping is not stored some where..."

Have you considered adding a table for that?

Its going to be a pain adding entries to your CASE statement (and it will perform slowly when the list becomes long).

But maybe there are only a few such towns, and they never add more?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-28 : 06:27:54

quote:
Originally posted by Kristen

" this mapping is not stored some where..."

Have you considered adding a table for that?

Its going to be a pain adding entries to your CASE statement (and it will perform slowly when the list becomes long).

But maybe there are only a few such towns, and they never add more?

I always prefer adding a table for above cases so that I just need to add one more record in case of addition of new town

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources