Month and quarter conversion

Function:

create or replace function mq2num(i_str in varchar2) return number is
  v_ret number;
begin
  if i_str not like '%Q' and i_str not like '%M' then
    return null;
  end if;

  begin
    v_ret := to_number(substr(i_str, 1, length(i_str)-1));
  exception when others then
    return null;
  end;

  if i_str like '%Q' then
    if v_ret not between 1 and 4 then
      return null;
    else
      v_ret := v_ret * 3;
    end if;
  elsif i_str like '%M' then
    if v_ret not between 1 and 12 then
      return null;
    end if;

  else
    return null;
  end if;

  return v_ret;

end mq2num;

Returns null if something is wrong, for instance bad parameter, I don't know what you want in this situation.

Test:

with t as (
  select column_value str 
    from table(sys.odcivarchar2list('1M', '2M', '12M', '1Q', '2Q', '3Q')))
select str, mq2num(str) mq,
    case 
      when str like '%M' then to_number(substr(str, 1, length(str)-1)) 
      when str like '%Q' then 3*to_number(substr(str, 1, length(str)-1)) 
    end val
  from t

STR            MQ        VAL
------ ---------- ----------
1M              1          1
2M              2          2
12M            12         12
1Q              3          3
2Q              6          6
3Q              9          9

As you see you don't need special function for this, but function is more comfortable.

something like this should do it

create or replace function l_convert (p_input in varchar2) return number is
  l_output number;
begin

  select substr(p_input,1,1) * decode(substr(p_input,2,1),'M',1,'Q',3)
    into l_output
    from dual;

  return l_output;

end;
/