SQL Reference - SUBSTRING

SUBSTRING(str, pos, len)

str: The original string from which a substring will be extracted.
pos: The starting position for extraction, with the first character in the string having the position of 1.
len: The length or number of characters to extract, starting from the position defined by pos.

Example

SELECT SUBSTRING('Hello World', 1, 5) AS ExtractString;

Output

+---------------+
| ExtractString |
+---------------+
| Hello         |
+---------------+

Explanation

The SUBSTRING function extracts a string from a given text field, starting at a certain position and for a certain length. In this example, it starts at position 1 and extracts 5 characters, resulting in the output ‘Hello’.

SUBSTRING(string FROM start FOR length)

string: The source string from which a substring is to be extracted.
start: The starting position from which the string extraction should begin. Positions start at 1 in SQL. If the specified start position is less than 1, the start is considered as position 1.
length: The number of characters to be extracted from the source string. If the length is not specified or greater than the length of the source string, the extraction continues to the end of the source string.

Example

SELECT SUBSTRING('Operational Database Management Systems' FROM 1 FOR 11) AS Result;

Output

Result
-----------
Operational

Explanation

SUBSTRING is used to extract a portion of a string. In this example, SUBSTRING is used to extract the first 11 characters from ‘Operational Database Management Systems’, which results in ‘Operational’.

SUBSTRING( expression , start , length )

expression: This parameter refers to the source string from which a part needs to be extracted.
start: This parameter signifies the starting position from which the extraction of the string should begin in the source string. The position of the first character is 1.
length: This parameter denotes the number of characters to be extracted from the source string starting from the “start” position.

Example

SELECT SUBSTRING('HELLO WORLD', 1, 5) AS ExtractString

Output

ExtractString
-----------
HELLO

Explanation

The SUBSTRING function in the example takes three arguments. The first is the string to be shortened, the second is the start position of the new string, and the third argument is the length of the new string. It converts ‘HELLO WORLD’ to ‘HELLO’ by extracting the first 5 characters.

SUBSTRING(string FROM position [FOR length])

string: This is the initial string from which a substring will be extracted. It can be the name of a column, a string literal, or the result of a function.
position: This is the start position for the substring. If this is a positive number, counting starts at the beginning of the string. If it’s a negative number, counting starts at the end of the string.
length (optional): This parameter specifies the number of characters to extract from the string, starting at the specified position. If this parameter is omitted, the function will extract until the end of the string.

Example

SELECT SUBSTRING('DataScience', 5, 3) FROM dual;

Output

aSc

Explanation

The SUBSTRING function in Oracle starts extraction at the 5th character of the string ‘DataScience’ and extracts 3 characters, resulting in ‘aSc’.

SUBSTRING(string, position, length)

string: The source text string from which a subsection is to be extracted.
position: The starting position where the extraction of the subsection should begin, where the initial character of the string is considered position one.
length: The number of characters to be included in the returned subsection, drawing from the ‘position’ defined.

Example

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

Output

Hello

Explanation

The SUBSTRING function in the given SQL statement is used to extract and return only a specified part of a string. In this case, ‘Hello World’ is the string, ‘1’ represents the start index of the substring, and ‘5’ specifies the length of the substring. Therefore, the function returns the first five characters of ‘Hello World’, which is ‘Hello’.