Can we use a CASE...WHEN inside SUBSTRING?

Question

(Here's a much simpler case of my actual conundrum at work)

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):

col
A3 
D2 
@5
#)
...

I want to use a select statement that outputs two columns called 'letter' and 'number' where 'letter' is the first character of 'col' & 'number' is the second character of 'col', but with the following mapping:

If substring(col FROM 1 for 1) in ('!','@','#'),
    then letter = 'A' and 'number' = substring(col FROM 2 for 1)

(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')

If col = '%9', 
    then 'letter' = 'H' and 'number' = '9'

(i.e., if a specific value in 'col' is '%9', then map it to 'letter' as 'H' and 'number' as '9')

If substring(col FROM 2 for 1) = '4',
    then 'letter' = substring(col FROM 1 for 1) and 'number' = '7'

(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')

Lastly, only select values where 'letter' is a letter and 'number' is a one character number. That is,

'letter' in ('A','B',...'Z') and 'number' in ('0','1',...'9')

What query would I run to solve this? That is, with the mapping hardcoded using CASE..WHEN?

Ideally, I'm trying to do something like:

SELECT substring((Case...When) FROM 1 for 1) AS letter,
substring((Case...When) FROM 2 for 1) AS number
FROM a;

Thanks!

Answer 1

Try this

SELECT * FROM (
SELECT
CASE 
  WHEN SUBSTRING(COL from 1 for 1) IN ('!','@','#') THEN 'A'
  WHEN COL LIKE '%9' THEN 'H'
  WHEN SUBSTRING(COL,2,1) = '4' THEN SUBSTRING(COL,1)
END Letter,
CASE 
  WHEN SUBSTRING(COL from 1 for 1) IN ('!','@','#') THEN substring(col FROM 2 for 1)
  WHEN COL LIKE '%9' THEN '9'
  WHEN SUBSTRING(COL,2,1) = '4' THEN '7'
END Number
FROM A
) 
WHERE (Letter ~ '^[A-Za-z]$') = true AND (Number ~ '^[0-9]$') = true;