oracle - Tricky use of substring -

my question field called contract_nm varchar2(14). need 3 different values use field filter clause.

here sample data , how long data might be, either 9 or 10 or 11, no more can be.

contract_nm               length(contract_nm) f.us.wz10                          9 f.us.wz11                          9 f.us.wz12                          9 f.us.rbz10                         10 f.us.rbz11                         10 f.us.rbz12                         10 f.us.zwaz10                     11 f.us.zwaz11                     11 f.us.zwaz12                     11 etc 

1) need display last 3 characters of contract_nm.

2) check last 3 characters of contract_nm see if first letter 1 of below, month , year next 2 letters , day defaulted first day of month. need display date because going date field.

trade months (terms): f              january                                 g             february                               h             march                    j              april                        k             may m       june                            n             july q             august u             september v             october x             november z       december 

3) bit difficult explain in writing, try , hope understand me!!! read contract_nm data, ignore first 5 characters (f.us.), ignore last 3 characters i.e. z11. now, working either 1 character (9 length) or 2 characters (10 length) or 3 characters (11 length) of data in middle, always, see below data. if 1 character, display letter, else if 2 characters, check 2nd letter if not ‘e’ or ‘a’ or ‘t’ display both letters, else display 1st letter. else if 3 characters, display first 2 letters.

4) need filter clause read contract_nm data, ignore first 5 characters (f.us.), ignore last 3 characters i.e. z11. now, working either 1 character (9 length) or 2 characters (10 length) or 3 characters (11 length) of data in middle, always, see below data. if 2 characters, check 2nd letter if not ‘e’ or ‘t’ don’t pull data, else it. else if 3 characters, check 3rd letter if not ‘e’ or ‘t’ don’t pull data, else it.

try following:

question 1:

  contract_nm_month_year := substr(contract_nm, -3); 

question 2:

  contract_date := case substr(contract_nm, -3, 1)                       when 'f' to_date('01-jan-' || substr(contract_nm, -2), 'dd-mon-rr');                       when 'g' to_date('01-feb-' || substr(contract_nm, -2), 'dd-mon-rr');                       when 'h' to_date('01-mar-' || substr(contract_nm, -2), 'dd-mon-rr');                       when 'j' to_date('01-apr-' || substr(contract_nm, -2), 'dd-mon-rr');                       when 'k' to_date('01-may-' || substr(contract_nm, -2), 'dd-mon-rr');                       when 'm' to_date('01-jun-' || substr(contract_nm, -2), 'dd-mon-rr');                       when 'n' to_date('01-jul-' || substr(contract_nm, -2), 'dd-mon-rr');                       when 'q' to_date('01-aug-' || substr(contract_nm, -2), 'dd-mon-rr');                       when 'u' to_date('01-sep-' || substr(contract_nm, -2), 'dd-mon-rr');                       when 'v' to_date('01-oct-' || substr(contract_nm, -2), 'dd-mon-rr');                       when 'x' to_date('01-nov-' || substr(contract_nm, -2), 'dd-mon-rr');                       when 'z' to_date('01-dec-' || substr(contract_nm, -2), 'dd-mon-rr');                       else null;                     end; 

question 3:

  display_3 := case length(contract_nm)                  when 9 substr(contract_nm, 6, 1);                  when 10                     case substr(contract_nm, 7, 1)                       when 'e' substr(contract_nm, 6, 1);                       when 'a' substr(contract_nm, 6, 1);                       when 't' substr(contract_nm, 6, 1);                       else substr(contract_nm, 6, 2);                     end;                  when 11 substr(contract_nm, 6, 2)                end; 

question 4:

  case length(contract_nm)           when 9 0 -- never pull data contract length = 9           when 10             case substr(contract_nm, 7, 1)               when 'e' 1               when 't' 1               else 0             end           when 11             case substr(contract_nm, 8, 10               when 'e' 1               when 't' 1               else 0             end           else 0         end = 1; 

share , enjoy.