Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create TABLE MY_TABLE
(NAME varchar2(100));
BEGIN
INSERT INTO MY_TABLE (NAME) VALUES ('XXX Peak Weekly W47-20');
INSERT INTO MY_TABLE (NAME) VALUES ('YYY WEEKLY Fut    W51-20');
INSERT INTO MY_TABLE (NAME) VALUES ('ZZZ Intra Fl W W51-20');
INSERT INTO MY_TABLE (NAME) VALUES ('TTT Base Week   W53-21');
INSERT INTO MY_TABLE (NAME) VALUES ('BBB Base Weeken W53-21');
INSERT INTO MY_TABLE (NAME) VALUES ('DDD BASE WEEK W53-21');
END;

/
1 rows affected

Select NAME, (
                CASE                    
                    WHEN SUBSTR(NAME, -7, 2) = ' W'
                    AND regexp_like(NAME, '\d\d$')
                    THEN TO_CHAR(to_date(TO_CHAR((to_number(SUBSTR(NAME, -5, 2))-1)*7 + DECODE
                        (TO_CHAR(to_date('01.01.'||20||SUBSTR(NAME, -2),'dd.mm.yyyy'),'D',
                        'NLS_DATE_LANGUAGE = American'),'1',1,'2',7,'3',6,'4',5,'5',4,'6',3,'7',2,0
                        )),'DDD', 'NLS_DATE_LANGUAGE = American'), 'MON',
                        'NLS_DATE_LANGUAGE = American')
                END  
         ) MONTH_TEXT from MY_TABLE
where SUBSTR(NAME, -7, 2) = ' W'
and regexp_like(NAME, '\d\d$')
ORA-01848: day of year must be between 1 and 365 (366 for leap year)