It may not be complicated but I cannot make it work.
I have to create a function for which the input is the F1 field of the attached file and output is the F2 foeld of the attached file.
If F1 is like test-dev%-% then F2 1ill be the string before the 2nd "-" character of F1. (Example: F1 = test-dev2-mile-mr1 Then F2= test-dev2)
If the above condition is not true AND if F1 is like test-dev% Then F2 is "none" (Example: F1 = test-dev2.5 Then F2= none)
If none of the above condition is true for F1, then F2 = Generic.
I wrote the attached code but it does not give correct values.
CREATE OR REPLACE FUNCTION MYFUN(f1 VARCHAR2) RETURN VARCHAR2 AS
IF (f1 LIKE 'test-dev%-%')
THEN r_value:= substr(r_value, (instr(r_value, '-', 1, 2)+1));
ELSIF (br_name LIKE 'test-dev%')
THEN r_value:= 'none';
ELSE r_value := 'Generic';