MySQL Convert alphabet to number (e.g ‘A’ to 1, ‘B’ to 2…)

I’m making mysql SP, which has an input of type VARCHAR(20).

What I want to do is to convert the last character of input string to a number.

However last character can be number or letter, so if it is number, converting is not needed.

For example, input ‘APPLE’ will be converted to ‘5’ because ‘E’ is 5 in alphabetical order, ‘123’ will be ‘3’, and ‘BANANA’ will be ‘1’.

To do this, I splitted last character but don’t know how to convert.

BEGIN

SET splited_str := RIGHT(UPPER(p_input), 1);

# with splited_str, convert letter to number in alphabetical order

END

I think that it would be possible with CASE ~ When, but is there any simpler way??

Answer

You can convert to a number using ASCII(). The logic you describe seems to be:

select (case when right(p_input, 1) between '0' and '9'
             then right(p_input, 1) + 0    -- convert digit to number
             else 1 + ascii(upper(right(p_input, 1))) - ascii('A')
        end)