I have below query in which for some security_typ field i am getting the correct value starting from month name for field Name but some security_type field the value is not starting from month name or there is no month name provided in that for field Name.
select distinct UPPER(SUBSTR(name, 0, instr(name,' ', 1, 1) - 1)) name, t.TYP as TYP, t.OPT_EXPIRE_DT from BB_EXPORT t WHERE 1 = 1;
Now i want to set the value in field MONAT which is number data type such that if the value is starting from field NAME is month name from January till December then set it to their month number for example if the starting name is August then set it to 8.
If the starting name is not any month from January till Decemeber as we can see for securtiy_typ'Financial index option' then take the month from field OPT_EXPIRY_DT and set it in MONAT field.
Currently in my select query i am using below condition to get the month number from Name field and setting it for field MONAT and i want to modify this select query:
Select to_number(TO_CHAR(to_date(UPPER(SUBSTR(name, 0, instr(name,' ', 1, 1) - 1)),'MONTH', 'NLS_DATE_LANGUAGE = American'),'MM')) monat from bb_export
Below is the desired result i am expecting:
