SQL Reference - SUBSTR

SUBSTR(string, position, length)

string: This parameter represents the source string from which a substring is to be extracted.
position: This parameter determines the starting point of the substring extraction within the source string. The position is a one based index unlike most programming languages which are generally zero based.
length: The length parameter specifies the number of characters to be extracted from the source string. If this parameter is not included, or if it is greater than the number of characters remaining from the position in the source string, then all remaining characters will be extracted.

Example

SELECT SUBSTR('Hello World', 1, 5);

Output

'Hello'

Explanation

The SUBSTR function is utilized in the SQL query to retrieve a substring starting from position 1 with a length of 5 characters from the string ‘Hello World’. The output, ‘Hello’, reflects this segment.

SUBSTR(string FROM start FOR length)

string: This parameter represents the original string from which the substring will be extracted.
start: This parameter marks the starting position for the substring extraction. The count begins at 1.
length: This parameter specifies the length of the substring to be extracted, starting from the position declared in the start parameter.

Example

SELECT SUBSTR('Hello World', 7, 5) as Substring;

Output

    substring
---------------
    World
(1 row)

Explanation

In this example, SUBSTR function is used to extract a substring from the string ‘Hello World’. The function starts at the 7th character and includes the next 5 characters, returning the string ‘World’.

SUBSTRING( expression , start , length )

expression: This parameter is the source string from which a substring is to be extracted.
start: This indicates the position in the source string where the extraction of the substring should commence. The count begins at 1.
length: This denotes the number of characters to be extracted from the source string as the substring. It starts counting from the ‘start’ position.

Example

SELECT SUBSTRING('SQL Server', 5, 3) AS ExtractString

Output

ExtractString
Ser

Explanation

In this example, the SUBSTRING function extracts three characters from the text ‘SQL Server’, starting from the fifth character. The output is the string ‘Ser’.

SUBSTR(string, start_position, substring_length)

string: This refers to the source string from which the substring will be extracted.
start_position: This is the position in the string where the extraction of the substring begins. The position count starts at 1.
substring_length: This specifies the length of the substring to be extracted. It counts the number of characters to be included starting from the start_position.

Example

SELECT SUBSTR('Oracle', 3, 2)
FROM dual;

Output

"ac"

Explanation

The SUBSTR function in Oracle extracts a substring starting from the third character (index begins at 1 in Oracle) of the string ‘Oracle’ up to the next 2 characters. The output “ac” is the result of this extraction operation.

SUBSTR(string, start_position, length)

string: This is the source string from which a substring will be extracted.
start_position: This defines the starting point from where the extraction of substring begins in the source string. It uses a 1-based index. If the starting point is positive, the function counts from the beginning of the string. If it’s negative, the function counts from the end of the string.
length: This is an optional parameter which determines the number of characters to be extracted from the source string. If this parameter is not provided, the function extracts the remainder of the source string from the given starting position.

Example

SELECT SUBSTR('Hello world', 7, 5) AS ExtractString;

Output

ExtractString
--------------
world

Explanation

In the above SQL query, the SUBSTR function is used to extract a substring from the string ‘Hello world’. The substring starts from the 7th position and is of length 5, which is ‘world’.