SUBSTR
Overview
The SUBSTR() function extracts a specific number of characters from a string.
Syntax
The syntax of the function is illustrated below:
2 Arguments
substr( string, start_position)
3 Arguments
substr( string, start_position, length )
string.Start Position
The start_position is used as the starting position, specifying the part from where the substring is to be returned. It is written as an integer value.
| Input | Return |
|---|---|
start_position < 0 ``start_position < string | The start_position is a given character in the string. The count starts from the first character. |
start_position > string | Returns an empty substring. |
start_position = negative value | The count starts from the provided negative value, with subsequent characters yielded as it approaches 0. If the index is less than or equal to 0, no characters are returned. Once it exceeds 0, characters from the string are yielded, starting from the first one. |
Length
The length is used to determine the number of characters to be extracted*. *It can be one or more characters.
| Input | Return |
|---|---|
length = 0 | Returns an empty substring. |
length is not set | The function will start from the specified start_position and end at the last character of the string. |
length = negative value | Returns an error. |
Examples
Case 1: SUBSTR() function with specified start_position & length
In this example, we will set the start_position with the first six characters and have five characters extracted:
SELECT substr('Watermelon',6,5) AS "Fruit";
The updated table is shown below:
Fruit
-------
melon
Case 2: SUBSTR() function with length = 0
The following query will extract a string with length = 0:
SELECT substr('Watermelon',6,0) AS "Fruit";
It will display an empty output as there is no length specified:
Fruit
-------
Case 3: SUBSTR() function with length = negative value
Here we will check if the length is specified with a negative value:
SELECT substr('Watermelon',6,-2) AS "Fruit";
Instead of extracting the string from the last characters, it will return an error as seen below:
ERROR: Length of substring cannot be negative
Case 4: SUBSTR() function with start_position > string
We know that Watermelon only has ten characters, but this time, we will figure out if the specified start_position is larger than the string’s characters:
SELECT substr('Watermelon',20,2) AS "Fruit";
It will display an empty output as shown below:
Fruit
-------
Case 5: SUBSTR() Function with 2 Arguments
In this example, we will set the start_position with the first six characters and have five characters extracted.
SELECT substr('database', 6) AS "Result";
It will display the substring from position 6 output as shown below:
Result
--------
ase