Not Equal to 'a','b' or 'c' - 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 2000 Forums

Transact-SQL (2000)

Not Equal to 'a','b' or 'c'

Author

Topic

erhimc
Starting Member

8 Posts

Posted - 2006-02-02 : 16:24:21

Hello,

I need to create a case statement or something to evaluate a field, and if it not equal to a,b,or c, then I need to turn it in to something else i.e. 'D'

any ideas ?

Ed

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-02-02 : 16:26:06

I think this will work for ya:


SELECT CASE MyField WHEN NOT IN ('a', 'b','c') THEN 'D' END as MyField
FROM MyTable

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

erhimc
Starting Member

8 Posts

Posted - 2006-02-02 : 16:38:50

Now that is some fast work :)

That works great, except if the data in the field IS one of those value, it is turning it to a NULL now...

i.e. Record one is a 'C' but now shows as a NULL, and record two is an 'S' and properly is showing as a 'D'.

Does that make sense ?

E.

erhimc
Starting Member

8 Posts

Posted - 2006-02-02 : 16:41:22

I just did a run on the data, and there are only four fields that have data that need to be changed...

could I do the reverse with this... Instead of testing for NOT IN, could I test for IN ?

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-02-02 : 16:47:50

yep

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-02-02 : 16:49:12

Opps, I missed part of your question.


SELECT CASE MyField WHEN IN ('a', 'b','c') THEN 'D' ELSE MyField END as MyField
FROM MyTable

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

erhimc
Starting Member

8 Posts

Posted - 2006-02-02 : 17:43:19

Michael...

Outstanding help. Works like a champ now :)

Thank You.

Ed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources