SUBSTR function in Oracle SQL

In Oracle SQL, the SUBSTR function is used to extract a substring from a string.

It has the following syntax:

SUBSTR(string, start_position [, length ])

The string argument is the string from which you want to extract a substring. The start_position argument specifies the starting position of the substring within the string, and the optional length argument specifies the number of characters to extract. If the length argument is omitted, the SUBSTR function will return all characters from the start_position to the end of the string.

Examples

Here are some examples of how to use the SUBSTR function:

SELECT SUBSTR('Hello, world!', 1, 5) FROM DUAL;

This query will return the string 'Hello', because it extracts 5 characters starting at the first character of the string 'Hello, world!'.

SELECT SUBSTR('Hello, world!', 7) FROM DUAL;

This query will return the string 'world!', because it extracts all characters starting at the seventh character of the string 'Hello, world!'.

SELECT SUBSTR('Hello, world!', -5, 3) FROM DUAL;

This query will return the string 'orld', because it extracts 3 characters starting at the fifth-to-last character of the string 'Hello, world!'.

What is difference between `SUBSTR` and `INSTR` in Oracle?

In Oracle SQL, the SUBSTR function is used to extract a substring from a string, while the INSTR function is used to find the position of a substring within a string.

The SUBSTR function has the following syntax:

SUBSTR(string, start_position [, length ])

The string argument is the string from which you want to extract a substring.
The start_position argument specifies the starting position of the substring within the string.
The optional length argument specifies the number of characters to extract. If the length argument is omitted, the SUBSTR function will return all characters from the start_position to the end of the string.

On the other hand, the INSTR function has the following syntax:

INSTR(string, substring [, start_position [, nth_appearance ] ])

The string argument is the string in which you want to search for a substring.
The substring argument is the substring that you want to find.
The optional start_position argument specifies the position within the string where the search should begin
The optional nth_appearance argument specifies which occurrence of the substring you want to find. If the nth_appearance argument is omitted, the INSTR function will return the position of the first occurrence of the substring.

To sum up, the SUBSTR function is used to extract a substring from a string, while the INSTR function is used to find the position of a substring within a string.

What is the difference between `SUBSTRING` and `SUBSTR`?

The SUBSTR function is specific to the Oracle SQL dialect, while the SUBSTRING function is more widely supported across different SQL dialects.

In general, the SUBSTRING function is preferred over the SUBSTR function because it is more portable across different SQL dialects. However, if you are working with Oracle SQL specifically, you can use the SUBSTR function instead.

Conclusion

In Oracle SQL, the SUBSTR function is used to extract a substring from a string.

SUBSTR function in Oracle SQL