SQL: Case value When value contains '+' THEN ...

[jluost1]

I would like to achieve the following result:
-------------------------
SELECT name FROM customer
WHERE
(CASE value
WHEN (value IS NOT NULL) AND CONTAINS(value, '+') THEN 0
ELSE CONVERT(INT value)
END) > 0
--------------------------
A few questions:
1. Can I use CONTAINS for NON-fulltext search?
2. Can I put multiple values after WHEN?
3. How can I make above work the way I want.

Thanks.

 

[MarkSweetland]

You could try CHARINDEX to see if the + is in the value field. Also can use multiple when clauses...

SELECT name FROM customer
WHERE 
(CASE 
  WHEN (value IS NOT NULL) AND CHARINDEX('+',value,1) > 0
     THEN 0
  WHEN (value IS NOT NULL) AND CHARINDEX('$',value,1) > 0
     THEN 0
  WHEN isnumeric(value) > 0 THEN CONVERT(INT value)
END) > 0

Mark