Oracle function to check String content

In Oracle 11gR2 I am trying to write a function like this so that the Input/Output are like:

Input/Output:
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
                  r_value   VARCHAR2(200);
BEGIN
    IF (inp LIKE 'st-test%')
    THEN r_value:= 'Category1';

    ELSIF (inp LIKE 'st-test%')
    THEN r_value:= 'Category2';

    ELSE  r_value := 'Category3';
    END IF;
return (r_value);
END;

Open in new window

toookiAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Here's one with regular expressions similar to your other question.
CREATE OR REPLACE FUNCTION MYFUNC(inp VARCHAR2) RETURN VARCHAR2 AS
BEGIN
	case
		when regexp_like(inp,'^st-test[0-9]*\.?[0-9]*$') then return('Caterogy1');
		when regexp_like(inp,'^st-test[0-9]*\.?[0-9]*[^0-9]+') then return('Category2');
		else return('Category3');
	end case;
END;
/

Open in new window

0
 
Helena Markováprogrammer-analystCommented:
I hope that it is not a homework :)

CREATE OR REPLACE FUNCTION MYFUNC(inp VARCHAR2) RETURN VARCHAR2 AS
                  r_value   VARCHAR2(200);
    n_number NUMBER;
BEGIN
    IF substr(inp,1,7)='st-test' THEN
      BEGIN
        select to_number(substr(inp,8)) INTO n_number from dual;
        r_value:= 'Category1';
      EXCEPTION
        when INVALID_NUMBER THEN
        r_value:= 'Category2';
      END;

    ELSE
      r_value := 'Category3';
    END IF;
    return (r_value);
END;
0
 
toookiAuthor Commented:
Many thanks. Both the solutions worked for me.

I also tried regexp_substr but incorrect.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.