In Oracle 11gR2 I am trying to write a function like this so that the Input/Output are like:
st-test1 / Category1
st-test2.5 / Category1
st-test5.5 / Category1
st-test4-whatever text / Category2
st-test4whatever text / Category2
st-test5.5whatever text / Category2
whatever text does not start with st-test / Category3
myline / Category3
If the input starts with "st-test" then concatenated by numbers(with or without a (one)decimal point) like 1, 2, 2.5, 7.5, 3.0 etc. but there is no other text/character/number/etc.
after that then the input belongs to Category1. If not in Category1 but the Input starts with "st-test" then it is Category2. If not in Category1 or Category2, then it is Category3.
How can a write such function...
I need to change the function I wrote so far below.
CREATE OR REPLACE FUNCTION MYFUNC(inp VARCHAR2) RETURN VARCHAR2 AS
IF (inp LIKE 'st-test%')
THEN r_value:= 'Category1';
ELSIF (inp LIKE 'st-test%')
THEN r_value:= 'Category2';
ELSE r_value := 'Category3';