SUBSTR in Oracle

In Oracle SQL, the SUBSTR function is used to extract a substring from a string. It allows you to specify the starting position and the length of the substring to extract.

Here's the syntax for the SUBSTR function in Oracle:

SUBSTR(string_expression, start_position, [length])

- string_expression: This is the string from which you want to extract the substring.

- start_position: This is the starting position within the string where the substring extraction should begin. The first character in the string has a position of 1.

- length (optional): This is the length of the substring to extract. If omitted, the SUBSTR function returns all characters from the start_position to the end of the string.

Let's see an example to understand how SUBSTR works in Oracle:

Suppose we have a string 'Hello, World!', and we want to extract a substring starting from the 7th position with a length of 5 characters.

SELECT SUBSTR('Hello, World!', 7, 5) AS substring_result FROM dual;

This query will return the result:

SUBSTRING_RESULT

World

In this example:

- We use the SUBSTR function to extract a substring from the string 'Hello, World!'.

- We specify the starting position as 7, and the length as 5.

- The extracted substring starts from the 7th position ('W') and has a length of 5 characters ('World').

If you omit the length parameter, SUBSTR will return all characters from the start_position to the end of the string. For example:

SELECT SUBSTR('Hello, World!', 7) AS substring_result FROM dual;

This would return 'World!', as it starts from the 7th position and continues to the end of the string.