Convert source number format to number

Question

I need to convert FIRST / SECOND / THIRD / FOURTH / FIFITH .... to 1 2 3 4 5 .... There is a function in oracle. Another way is possible as shown below, but I want the opposite

SELECT TO_CHAR(to_date(3,'J'), 'fmJSPTH') FROM dual; 

Answer 1

Good question!

 create table t (w varchar2(30)); insert into t values ('First'); insert into t values ('THIRD'); insert into t values ('eleventh'); insert into t values ('Twenty-eighth'); insert into t values ('two hundred fifty sixth'); select tw, dict.n from t left join ( select level n, regexp_replace(to_char(to_date(level,'J'),'fmJSPTH'),'[^AZ]') w FROM dual connect by level <= 1000 ) dict on regexp_replace(upper(tw),'[^AZ]')=dict.w 

fiddle