substr usage in oracle

The SUBSTR function in Oracle extracts a specific number of characters from a string. The syntax is SUBSTR(string, start_position, length), where string is the target string, start_position is the starting extraction position (starting from 1), and length is the number of characters extracted. A negative starting position means counting down from the end of the string, and omitting the length parameter extracts all characters from the starting position to the end of the string.

SUBSTR function in Oracle

SUBSTR function overview

The SUBSTR function is used to extract a specified number of characters from a given string. The syntax is as follows:

<code class="sql">SUBSTR(string, start_position, length)</code>

Where:

• string: The string from which characters are to be extracted.
• start_position: The position where you want to start extracting characters, starting from 1.
• length: The number of characters to extract.

SUBSTR function example

Suppose we have a string named "customer_name" whose value is "John Doe".

• Extract the first three characters: SUBSTR("customer_name", 1, 3) will return "Joh".
• Extract five characters starting from the fourth character: SUBSTR("customer_name", 4, 5) will return "n Doe".
• Extract the tüm characters starting from the third to last character: SUBSTR("customer_name", -3) will return "Doe".

Negative starting position

The starting position can be a negative number, which means counting down from the end of the string. For example:

• Extract the last four characters: SUBSTR("customer_name", -4) will return "Doe".

Missing length parameter

If the length parameter is omitted, the SUBSTR function will extract all characters from the starting position to the end of the string. For example:

• Extract the tüm characters starting from the sixth character: SUBSTR("customer_name", 6) will return "Doe".

Variations of the SUBSTR function

The SUBSTR function also has the following variants:

• INSTR: Used to find the first occurrence of a substring in a string.
• SUBSTRB: Used to extract characters starting from the specified byte position.
• SUBSTRC: is used to extract characters starting from the specified character position.

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