Detailed explanation of oracle substr function usage

oracle substr function usage: 1. To extract the entire string, you can set start_position to 1 and length to the length of the string; 2. To extract the substring, you can specify the values ​​of start_position and length; 3. Extract For a substring starting from the specified position, you can set the length value to the string length minus start_position plus 1; 4. Extract the last few characters; 5. Extract the substring from the specified position to the end of the string, etc.

Oracle's SUBSTR function is a function used to extract a substring from a string. Its syntax is as follows:

SUBSTR(string, start_position, length)

Among them, string is the original string to extract the substring, start_position is the position to start extracting, length is to extract The length of the substring.

The usage of this function is explained in detail below.

Extract the entire string

If you only want to extract the entire string, you can set start_position to 1 and length to the length of the string. For example, assuming there is a string as "Hello World", we can extract the entire string using the following statement:

SELECT SUBSTR('Hello World', 1, LENGTH('Hello World')) FROM DUAL;

This will return "Hello World".

Extract substring

If you only want to extract a part of the string, you can specify the values ​​of start_position and length. For example, assuming there is a string as "Hello World", we can extract the substring "World" using the following statement:

SELECT SUBSTR('Hello World', 7, 5) FROM DUAL;

This will return "World".

Extract the substring starting from the specified position

If you only want to extract the substring starting from the specified position, you can set the value of length to the string Length minus start_position plus 1. For example, assuming there is a string "Hello World", we can use the following statement to extract the substring "World" starting from the 6th character:

SELECT SUBSTR('Hello World', 6, LENGTH('Hello World')-5) FROM DUAL;

This will return "World".

Extract the last few characters

If you want to extract the last few characters of the string, you can set the value of start_position to the length of the string minus the length to be extracted The number of characters is increased by 1, and the value of length is set to the number of characters to be extracted. For example, assuming there is a string called "Hello World", we can extract the last 5 characters of "World" using the following statement:

SELECT SUBSTR('Hello World', LENGTH('Hello World')-4, 5) FROM DUAL;

This will return "World".

Extract the substring from the specified position to the end of the string

If you only want to extract the substring from the specified position to the end of the string, you can change start_position The value is set to the starting position to be extracted, while the value of length is set to the length of the string minus start_position plus 1. For example, assuming there is a string as "Hello World", we can use the following statement to extract the substring "World" from the 7th character to the end:

SELECT SUBSTR('Hello World', 7, LENGTH('Hello World')-6) FROM DUAL;

This will return "World".

Summary:

Oracle's SUBSTR function is a function used to extract a substring from a string. It can extract the entire string as needed, extract a substring at a specified position and length, extract a substring starting at a specified position, extract the last few characters, and extract a substring from a specified position to the end of the string. According to different needs, we can flexibly use the SUBSTR function to meet our requirements.

The above is the detailed content of Detailed explanation of oracle substr function usage. For more information, please follow other related articles on the PHP Chinese website!