title: Using CHARINDEX and SUBSTRING in a CASE statement
body: <p>I need to evaluate a field with a CASE statement.  The field name is Commodity and is a varchar(255).  The values differ and I need to extract a specific portion from it after a specific character.  The character is a '>'. I was able to come up with the value I want returned after the > by using the following code:</p>
  
  <pre><code>SUBSTRING(Commodity, CHARINDEX('&gt;', Commodity) + 2, LEN(Commodity))
  </code></pre>
  
  <p>I am however unsure of how to work this into my CASE statement.  I need to test for Is Null and then just assign it a value of 'No Commodity'.  Then I need to test for the presence of a > and then implement the code above to return the value.  Then I need to test for when there is no > but it is not null and just return the value of the Commodity field.</p>