Pattern match in a text column

I have a text column and the data in the text columns are as below:

Rob goes to school,get punished
Rob goes to school
Rob does not goes to school,get punished

When trying to write a query using case statement like

CASE 
    WHEN (PATINDEX('%Rob goes to school%',value) > 0) OR
         (PATINDEX('%Rob is ill%',value) > 0 ) AND
         (PATINDEX(%get p开发者_如何转开发unished%',value) > 0) THEN
           'DONE'

It should select only the 1st statement but instead it is picking both the 1st and 2nd statement with 'DONE'. Any suggestion how to do a pattern match in this case? I am using SQL Sever 2005/2008

Operator precedence and not enough parenthesis probably

You have x OR y AND z which is actually x OR (y AND z). Do you want want (x OR y) AND z?

• The 2nd statement give true OR (false AND false) which gives true
• You want (true OR false) AND false to give false

So the SQL should be

CASE WHEN
   (
      PATINDEX('%Rob goes to school%', value) > 0
      OR
      PATINDEX('%Rob is ill%', value) > 0
   )
   AND
   (PATINDEX(%get punished%', value) > 0) THEN 'DONE'
...

PATINDEX does not treat your strings as delimited lists (comma-separated values) -- it searches for a match against the entire string.

Rob goes to school,get punished
Rob goes to school

PATINDEX('%Rob goes to school%',value) > 0 evaluates to true for both of them because the wildcard % matches any string of 0 or more characters. Your second and third patterns never get evaluated.

If you want to test which pattern is returning true, try this:
CASE 
    WHEN (PATINDEX('%Rob goes to school%',value) > 0) THEN 'Pattern 1'
    WHEN (PATINDEX('%Rob is ill%',value) > 0 ) THEN 'Pattern 2'
    WHEN (PATINDEX('%get punished%',value) > 0) THEN 'Pattern 3'
    ELSE 'No Match Found' END

If you want a pattern to match the first value, but not the second, then look for (PATINDEX('%Rob goes to school,%',value) > 0) with the comma instead.

Otherwise -- if you're wanting to treat the strings like comma-separated values, PATINDEX is not your best tool for that. Other options might include converting your strings to tables via table-value function, or what have you.