case statements using logical expressions

[bebblebrox]

Hi,

I have the following snippit:

       select case addr2
        	when charindex(addr,'@') > 0 then null
        	else addr2
        end as addr2 from members

when i run it i get:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '>'.

what gives? addr2 is a varchar(100)

 

[Catadmin]

You forgot your = sign and your syntax is wrong.

select addr2 =
        case
            when charindex(addr,'@') > 0 then null
            else addr2
        end as addr2 from members

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[jbenson001]

Also the parmeters for the CharIndex function need to be reversed.

 

[bebblebrox]

i don't think i can use an "=" because it's part of a larger select statment, ie

SELECT
  blah,
  blah,
  blah,

  case addr2
     when charindex(addr2,'@') > 0 then null
     else addr2
  end as addr2,

  blah,
FROM members

What's wrong with my syntax? I can't see a differrence between mine and yours.

 

[jbenson001]

Use Catadmin's code and remove the "AS addr2" text.

 

[Catadmin]

Your syntax has the word CASE before the word Addr2. Check both your posts. It needs to be swapped. Case should be AFTER your field declaration. As far as using CASE in a Select statement with multiple fields, you do need to use an = sign. Look up CASE in Books Online if you don't believe me.

Example C shows how you do it when you have columns/values picked before the CASE statement.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???