CASE WHEN theRank = 'A1' THEN 1 WHEN theRank = 'A2' THEN 2 WHEN theRank = 'A3' THEN 3 WHEN theRank = 'A4' THEN 4 WHEN theRank = 'A5' THEN 5 WHEN theRank = 'A6' THEN 6 WHEN theRank = 'A7' THEN 7 WHEN theRank = 'A8' THEN 8 WHEN theRank = 'A9' THEN 9 WHEN SUBSTRING(theRank,1,1) = 'O' THEN 10 ELSE 0 END AS theRankN,

The problem is that on any number of the individual records, the value assigned is flat-out wrong. For example, I tested the substring function completely standalone, and it correctly pulls out the 'O' But then the statement assigns a 4. Similarly, 'A4' sometimes returns a 10.

Can anyone provide some ideas on what I've got wrong?