SUBSTR and INSTR functions in Oracle SQL

In SQL, both the SUBSTR (or SUBSTRING in some databases) and INSTR functions are used to work with strings, but they serve different purposes. Here's a breakdown of each function and how they differ:

1. SUBSTR (or SUBSTRING) Function

The SUBSTR function is used to extract a substring from a given string. It returns a portion of the string starting at a specified position for a specified length.

Syntax:

SUBSTR(string, start_position, length)

string: The string from which to extract the substring.

start_position: The position (starting from 1) where the substring should start. In some databases, the position might be zero-based (e.g., SUBSTRING in SQL Server).

length (optional): The number of characters to extract. If omitted, it extracts the substring from the starting position to the end of the string.

Example:

SELECT SUBSTR('Hello, World!', 1, 5) FROM dual; -- Output: 'Hello'

This extracts the first 5 characters from the string 'Hello, World!'.

Example with no length specified:

SELECT SUBSTR('Hello, World!', 8) FROM dual; -- Output: 'World!'

This extracts the substring starting at position 8 and goes until the end of the string.

1. INSTR Function

The INSTR function is used to find the position of a substring within a string. It returns the position (index) of the first occurrence of the substring. If the substring is not found, it returns 0.

Syntax:

INSTR(string, substring, start_position, match_occurance)

string: The string in which to search.

substring: The substring you want to find.

start_position (optional): The position from which to start searching. Default is 1.

match_occurance (optional): The occurrence of the substring you want to find. Default is 1 (first occurrence).

Example:

SELECT INSTR('Hello, World!', 'World') FROM dual; -- Output: 8

This returns the position of the substring 'World' within the string 'Hello, World!', which is position 8.

Example with optional parameters:

SELECT INSTR('Hello, World, World!', 'World', 1, 2) FROM dual; -- Output: 14

This finds the second occurrence of 'World' starting from position 1, which is at position 14.

Key Differences Between SUBSTR and INSTR:

Example Comparison:

1. Using SUBSTR to extract a part of the string:

SELECT SUBSTR('Oracle SQL Tutorial', 8, 3) FROM dual; -- Output: 'SQL'

1. Using INSTR to find the position of a substring:

SELECT INSTR('Oracle SQL Tutorial', 'SQL') FROM dual; -- Output: 8

Summary:

Use SUBSTR when you want to extract a portion of a string.

Use INSTR when you want to find the position of a substring within a string.