Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle function to check String content

Posted on 2011-05-08
3
Medium Priority
?
557 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:toooki
3 Comments
 
LVL 22

Assisted Solution

by:Helena Marková
Helena Marková earned 948 total points
ID: 35718223
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 952 total points
ID: 35719865
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
 

Author Comment

by:toooki
ID: 35733053
Many thanks. Both the solutions worked for me.

I also tried regexp_substr but incorrect.

Thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

576 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question