CASE statement with LIKE statement

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 statement with LIKE statement

Author

Topic

2k2er
Starting Member

20 Posts

Posted - 2012-01-25 : 13:45:26

Hi Team,

I have got a requirement to differentiate a list of machines from one of my table based on the naming convention. So, i was trying to make a CASE statement to get the same but landed up in trouble since i do not know how to use LIKE statement within CASE statement. I have tried below query.

SELECT DISTINCT
a.Name0,
d.sms_installed_sites0 as 'sitecode',
CASE a.Name0
when (select Name0 from v_r_system where Name0 like 'BA%') THEN 'Atlanta'
when (select Name0 from v_r_system where Name0 like 'BC%') THEN 'Brazil'
ELSE 'Unknown'
end
FROM v_r_system AS a
left outer join v_RA_System_SMSInstalledSites d
on a.resourceid=d.resourceid
WHERE
a.Operating_System_Name_and0 not like '%server%'
AND (a.Client0=1 and a.Obsolete0=0 and a.active0=1)

Can anyone help me out what is the right syntax if we have with CASE statement or any other methods to get it done?

Thanks,
2k2er

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-25 : 13:46:50

You've shown us the code that isn't working, but you haven't explained what you want it to do. We'll need an explanation as well as sample data. We can't read minds with just the query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-25 : 13:48:44

quote:
Originally posted by 2k2er

Hi Team,

I have got a requirement to differentiate a list of machines from one of my table based on the naming convention. So, i was trying to make a CASE statement to get the same but landed up in trouble since i do not know how to use LIKE statement within CASE statement. I have tried below query.

SELECT DISTINCT
a.Name0,
d.sms_installed_sites0 as 'sitecode',
CASE
when a.Name0 like 'BA%' THEN 'Atlanta'
when a.Name0 like 'BC%' THEN 'Brazil'
ELSE 'Unknown'
end as ColumnName
FROM v_r_system AS a
left outer join v_RA_System_SMSInstalledSites d
on a.resourceid=d.resourceid
WHERE
a.Operating_System_Name_and0 not like '%server%'
AND (a.Client0=1 and a.Obsolete0=0 and a.active0=1)

Can anyone help me out what is the right syntax if we have with CASE statement or any other methods to get it done?

Thanks,
2k2er

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2012-01-25 : 13:51:55

I'll guess!


CASE 
when a.Name0 like 'BA%' THEN 'Atlanta'
when a.Name0 like 'BC%' THEN 'Brazil'
ELSE 'Unknown'
end

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-25 : 13:52:40

quote:
Originally posted by tkizer

You've shown us the code that isn't working, but you haven't explained what you want it to do. We'll need an explanation as well as sample data. We can't read minds with just the query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

I'm sorry Tara. In most cases I hate the situation when I have asked a poster for more details and then to see that another one meaned to give an answer without waiting for more details...
But in this case I thought the problem was clear - ar least to me.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2012-01-25 : 13:53:28

Yeah, its still a coin-toss though

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-25 : 13:55:28

It definitely isn't clear to me.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

2k2er
Starting Member

20 Posts

Posted - 2012-01-25 : 14:11:28

Sorry guys,

The sample data will look like this if i execute below query

SELECT DISTINCT
a.Name0,
d.sms_installed_sites0 as 'sitecode'
FROM v_r_system AS a
left outer join v_RA_System_SMSInstalledSites d
on a.resourceid=d.resourceid

BA220WV1 SMS
BA221WV1 NULL
BA223W01 SMS
BA223WV1 SMS
BAHSTR6052652LT SMS
BAHSTR6055537LT SMS
BANSTR6045033LT SMS
BANSTR6054847LT SMS
BC104WL1 SMS
BC111WV1 SMS
BC160WL1 SMS
BC160WV3 NULL
BC203WL1 SMS
BC209W01 NULL
BWTRNQMH04 SMS
BWTRNQMH05 SMS
BWTRNQMH08 SMS
BWTRNQMH10 SMS
BWTRNQMH11 SMS
BWTRNQMH13 SMS
BWTRNQMM01 SMS

Now i want to differentiate all machines starts from BA, BC and BW...Hence i thought i can give a try with CASE statement..That is,

If a machine starts with BA then i want to have another column says 'Atlanta'
If starts with BC then 'Brazil'

etc etc

So, that's where i come up with my code in my first post here..and guys sorry for not being clear very first time...I thought i have given enough info...

:-)

Thanks,
2k2er

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-25 : 14:18:04

Did you try Fred's code? He corrected it inside the quote he did.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

2k2er
Starting Member

20 Posts

Posted - 2012-01-25 : 14:26:03

That does not help..

I am getting below error..

Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near 'a'.

Note: I am using SQL 2000 and SQL 2005 (tried in both)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-25 : 14:54:04

It is always a good idea to show the real executed statement which gives an error.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-25 : 14:58:44

Just a guess: you have not deleted that a.Name0 right after the word CASE...

No, you're never too old to Yak'n'Roll if you're too young to die.

2k2er
Starting Member

20 Posts

Posted - 2012-01-25 : 15:30:54

This is what i executed!

SELECT DISTINCT
a.Name0,
d.sms_installed_sites0 as 'sitecode',
CASE
when a.Name0 like 'BA%' then 'Atlanta'
when a.Name0 like 'BC%' then 'Brazi'
else 'Unknown'
end as 'Departement'
FROM v_r_system AS a
left outer join v_RA_System_SMSInstalledSites d
on a.resourceid=d.resourceid
a.Operating_System_Name_and0 not like '%server%'
AND (a.Client0=1 and a.Obsolete0=0 and a.active0=1)

2k2er
Starting Member

20 Posts

Posted - 2012-01-25 : 15:44:52

Guys,

All glory to me!!

I got it working...looks like the alias is the culprit..

SELECT DISTINCT
Name0,
d.sms_installed_sites0 as 'sitecode',
CASE
when Name0 like 'BA%' then 'Atlanta'
when Name0 like 'BC%' then 'Brazi'
else 'Unknown'
end as 'Departement'
FROM v_r_system AS a
left outer join v_RA_System_SMSInstalledSites d
on a.resourceid=d.resourceid
where a.Operating_System_Name_and0 not like '%server%'
AND (a.Client0=1 and a.Obsolete0=0 and a.active0=1)

Thanks a lot for your quick responses...Much appreciated!

2k2er

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources