Case Statement Help - 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

General SQL Server Forums

New to SQL Server Programming

Case Statement Help

Author

Topic

bingo7
Starting Member

2 Posts

Posted - 2010-01-20 : 11:50:29

Hi -

I am trying to write a statement that creates a new column of data based on whether another column has a specific value(s). I think a case statement would work but really stuck. The data in Col 1 is a string in a format like xx-xxx; xx-xx. There could be several of these values in column 1. here is a snippet of the data:

Specialization Exams
70-228; 70-229; 70-631
70-229
70-431; 70-630; 70-631
70-228; 70-229; 70-431
70-228; 70-229
70-445
70-229
70-228; 70-229
70-229
70-228; 70-229
70-229
70-228; 70-229; 70-431
70-228; 70-229; 70-431; 70-445; 70-446; 70-630

I want something like this, but i know this won't work:

SELECT [Partner ID], [Organization Name], [Specialization Exams], Exam1 =
case
when [Specialization Exams]= '70-229' then 1 or
when [Specialization Exams]= '70-446' then 1 or
when [Specialization Exams]= '70-448' then 1 or
when [Specialization Exams]= '70-452' then 1 or
when [Specialization Exams]= '70-455' then 1 or
else 0
end
FROM Exams$

Any help would be greatly appreciated!

B

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-20 : 12:02:31

Try this:


SELECT [Partner ID], [Organization Name], [Specialization Exams],
case 
when [Specialization Exams] like '%70-229%' OR 
     [Specialization Exams] like '%70-446%' OR 
     [Specialization Exams] like '%70-448%' OR
     [Specialization Exams] like '%70-452%' OR
     [Specialization Exams] like '%70-455%' 
then 1 
else 0
end as Exam1
FROM Exams$

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