title: Transact SQL CASE over a variable length input_expression
body: <p>I have to produce an ad-hoc report on the number of transactions made with different credit card types. For the purposes of the report it is fine to assume that all credit cards that start with a 4 are VISA cards and that those that start with a 5 are MasterCard.</p>
  
  <p>This query works well for the above distinctions:</p>
  
  <pre><code>select card_type = 
      case substring(pan,1,1) 
          when '4' then 'VISA'
          when '5' then 'MasterCard'
          else 'unknown' 
      end,count(*),
      sum(amount)
  from transactions
  group by card_type
  </code></pre>
  
  <p>However in our situation (not sure how this works world wide) all cards that start with a 3 can be considered Diners Club Cards except for those that start with a 37 which are AMEX cards.</p>
  
  <p>Extending the above query like this seems like a complete hack</p>
  
  <pre><code>select card_type = 
      case substring(pan,1,2) 
          when '30' then 'Diners'
          ...
          when '37' then 'AMEX'
          ...
          when '39' then 'Diners'
          when '40' then 'VISA'
          ...
          when '49' then 'VISA'
          when '50' then 'MasterCard'  
          ...
          when '59' then 'MasterCard'  
          else 'unknown' 
      end,count(*),
      sum(amount)
  from transactions
  group by card_type
  </code></pre>
  
  <p>Is there an elegant way of grouping by the first digit in all cases except where the first two digits match the special case?</p>
  
  <p><em>I also have no idea how to</em> Title <em>this question if anyone wants to help out...</em></p>
  
  <p><strong>EDIT</strong>: I had the values for MasterCard and VISA mixed up, so just to be correct :)</p>