Oracle obtains the desired data analysis from the string resource

[Oracle] Get the data analysis you want from a string resource
Requirements: Order analysis, according to the game, account level, game account occupation, district clothing, price range analysis of the transaction data movement

。
Target: Order Form
Processing analysis: The order can be read directly to the marked Games, district services, prices. And there are already relevant configurations that can be associated with this class

Information.
Problem Analysis 1: Account occupation, account level and other transaction information is not detailed in the order, can only be obtained through other information

Take.
Handling Ideas 1: Approximate analysis of occupation and account levels through the headings in the order.
The title format is as follows:

Process ideas: Extract the occupation and account level from the title.
Occupational:
Substr
(
O5.bizoffername,
InStr (O5.bizoffername, ' "', 1, 1) +1,
InStr (O5.bizoffername, ",")-(InStr (O5.bizoffername, ' "', 1, 1) +1)
)
Level:
Substr
(
O5.bizoffername,
InStr (O5.bizoffername, ", 1, 3) +1,
InStr (O5.bizoffername, ' class ', ")-(InStr (O5.bizoffername,", 1, 3) +1)
)
Processing results

Problem Analysis 2: Levels similar to ' 50 ' are extracted from the string as parameters and account level interval dimensions are compared

Invalid characters will occur.
Treatment Ideas 2: The decisive thought of converting characters into number,to_number.
Problem Analysis 3: Invalid characters are encountered after conversion to To_number.
Processing Ideas 3: There are other problems, so a custom function is_number is created.
CREATE OR REPLACE FUNCTION is_number (parameter VARCHAR2) RETURN number is
Val number;
BEGIN
Val: = To_number (NVL (parameter, ' a '));--Copy the character a if the parameter is null
RETURN 1;
exception--when encountering ' 40 ' such to_number conversion normal, when encountered ' 40 ' when the To_number abnormal return 0,
When OTHERS Then
RETURN 0;
END;
Then I looked at the basic data that was dealt with in thought 1, with where is_number (level) = 0, and found that there were really few

Dealing with an irregular ' Hello 20 ' similar level field and number interval judge the error when it comes to the root of the problem. So I had to drain it.

These dozens of irregular data. Finally get the business needs:

Oracle obtains the desired data analysis from the string resource