Application and Analysis of substr () function in Oracle

1) substr function format (commonly known as: intercept function)

Format 1:substr (string string, int a, int b);

Format 2:substr (string string, int a);

Explain:

Format 1

1. String to be intercepted by string
2. A intercept the starting position of the string (note: When a equals 0 or 1 o'clock, it is intercepted from the first bit)
3, b The length of the string to intercept

Format 2

1. String to be intercepted by string
2, a can be understood as starting from the first character to intercept all the following strings.

2) Example parsing

Instance:

1, substr ("HelloWorld", 0, 3);  //Return result: Hel, intercept starting from H 3 characters
2, substr ("HelloWorld", 1, 3) ; //Return Result: Hel, intercept starting from H 3 characters
3, substr ("HelloWorld", 2,3); //Return result: ell, intercept starting from E 3 characters
4, substr ("HelloWorld ", 0,100); //Return Result: helloworld,100 The maximum number of preprocessed strings is returned, although it exceeds the length of the preprocessed string, but does not affect the return result.
5, substr ("HelloWorld", -1,3); //return result: D
6, substr ("HelloWorld", -2,3); //return Result: LD
Span style= "COLOR: #0000ff" >7, substr ("HelloWorld", -3,3); //return Result: Rld
8, substr (" HelloWorld ", -4,3); //return result: ORL

( Note: When a equals 0 or 1 o'clock, it is intercepted from the first bit (for example: 1 and 2) )
( note: Although the 5, 6, 7, 8 intercept are 3 characters, the result is not 3 characters, as long as the a<b, take the number of a (such as: 5, 6, 7), when a >b the number of B (for example: 7 and 8) )

9, substr ("HelloWorld", 0); //Return Result: HelloWorld, intercept all characters
10, substr ("HelloWorld", 1); //Return Result: HelloWorld, intercept all characters
11, substr ("HelloWorld", 2); //Return result: Elloworld, Intercept all characters after starting with E
12, substr ("HelloWorld", 3); //Return Result: Lloworld, intercept all characters since L
13, substr ("HelloWorld", -1); //return Result: D, starting from the last D to intercept 1 characters
14, substr ("HelloWorld", -2); //Return Result: LD, starting from the last D to intercept 2 characters
15, substr ("HelloWorld", -3); //Return Result: Rld, 3 characters back from the last D

( Note: When there are only two parameters, either negative, it is intercepted from the last start (e.g. 13, 14, and).)

Graphic:

1.

2.

5.

6.

7.

8.

13.

14.

15.

3) Complete Function instance

Create or Replace function Get_request_codereturnVARCHAR2 as--function: Automatically generate a number v_mca_no mcode_apply_01.mca_no%TYPE; CURSOR Get_max_mca_no is SELECT max (substr (Mca_no, One,1)--The maximum number to be detected, intercept the last three bits, such as 001,002... 00n from mcode_apply_01 WHERE substr (Mca_no,3,8) = To_char (Sysdate,'YYYYMMDD'); --Generate (starting from third place) date "such as:20170422"V_requestcode VARCHAR2 (3);        BEGIN OPEN Get_max_mca_no;        FETCH get_max_mca_no into V_requestcode;  CLOSE Get_max_mca_no; IF V_requestcode is NULL and then V_requestcode:= NVL (V_requestcode,0);       --NVL () function: When V_requestcode is null, take 0 as the value END IF; V_requestcode:= Lpad (V_requestcode +1,3,'0'); --Adds 1 to the value that is intercepted in the cursor, and then fills 0 to the left, creating001... 00n sequence number; Lpad () function: Fill V_mca_no to the left:='MA'|| To_char (Sysdate,'YYYYMMDD') ||  V_requestcode; --the resulting application number (eg: ma20170422001;ma20170422002; ma2017042200n) RETURN'0~,'||V_mca_no; END;

Application and Analysis of substr () function in Oracle