title: Can we use a CASE...WHEN inside SUBSTRING?
body: <p>(Here's a much simpler case of my actual conundrum at work)</p>
  
  <p>Let's say I have a table, called 'a', with a column named 'col' with the following values (say a column of length 2 with many random combination of characters):</p>
  
  <pre>
  <b><i>col</i></b>
  A3 
  D2 
  @5
  #)
  ...
  </pre>
  
  <p>I want to use a select statement that outputs two columns called 'letter' and 'number' where 'letter' is the first character of 'col' &amp; 'number' is the second character of 'col', but with the following mapping:</p>
  
  <pre><code>If substring(col FROM 1 for 1) in ('!','@','#'),
      then letter = 'A' and 'number' = substring(col FROM 2 for 1)
  </code></pre>
  
  <p>(i.e., if the first character of something in 'col' is '!', '@', or '#', map it
  to 'letter' as 'A' while keeping the second character of 'col' the same and 
  mapping that value to 'number')</p>
  
  <pre><code>If col = '%9', 
      then 'letter' = 'H' and 'number' = '9'
  </code></pre>
  
  <p>(i.e., if a specific value in 'col' is '%9', then map it to 'letter' as 'H' and
  'number' as '9')</p>
  
  <pre><code>If substring(col FROM 2 for 1) = '4',
      then 'letter' = substring(col FROM 1 for 1) and 'number' = '7'
  </code></pre>
  
  <p>(i.e., if the second character of a value in 'col' is '4', leave the first
  character unchanged and map it to 'letter' and map the second character to
  'number' as '7')</p>
  
  <p>Lastly, only select values where 'letter' is a letter and 'number' is a one
  character number. That is, </p>
  
  <pre><code>'letter' in ('A','B',...'Z') and 'number' in ('0','1',...'9')
  </code></pre>
  
  <p>What query would I run to solve this? That is, with the mapping hardcoded using CASE..WHEN?</p>
  
  <p>Ideally, I'm trying to do something like:</p>
  
  <pre><code>SELECT substring((Case...When) FROM 1 for 1) AS letter,
  substring((Case...When) FROM 2 for 1) AS number
  FROM a;
  </code></pre>
  
  <p>Thanks!</p>