| 21. 5. 6. SUBSTR |
|
|
SUBSTR retrieves part of existing string: |
|
v_tx:= substr(string, start position[,number of chars]);
|
|
- The start position could be either a positive or negative integer.
- Positive value would let SUBSTR start counting the position from the beginning.
- Negative value would let SUBSTR start counting the position from the end of the string.
- If your starting point is more than the total number of characters in the string, Oracle returns NULL.
|
|
SQL> declare
2 v1_tx VARCHAR2(5):='ABCDE';
3 v2_tx VARCHAR2(5);
4 begin
5 v2_tx:=substr(v1_tx,2);
6 DBMS_OUTPUT.put_line(v2_tx);
7 v2_tx:=substr(v1_tx,-2);
8 DBMS_OUTPUT.put_line(v2_tx);
9 end;
10 /
BCDE
DE
PL/SQL procedure successfully completed.
|
|
The number of characters requested from the string might not always be the length of the resulting string. |
You might request more characters than the string has. |
Oracle just returns everything up to the end of the string. |
|
SQL>
SQL> declare
2 v1_tx VARCHAR2(5):='ABCDE';
3 v2_tx VARCHAR2(5);
4 begin
5 v2_tx:=substr(v1_tx,2,2);
6 DBMS_OUTPUT.put_line(v2_tx);
7 v2_tx:=substr(v1_tx,2,7);
8 DBMS_OUTPUT.put_line(v2_tx);
9 end;
10 /
BC
BCDE
PL/SQL procedure successfully completed.
|
|