SUBSTR

10min

Overview
The SUBSTR() function extracts a specific number of characters from a string:
Syntax
substr( string, start_position, length )
substr( string, start_position, length )
﻿
📌 Input and return will be of type 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 = 1
The count starts from the first character.
start_position < 0 
start_position < string
The start_position is set to 1, and the count starts from the first character.
start_position > string
Returns an empty substring.
start_position = negative value
The count starts from the end of the string.
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
SELECT substr('Watermelon',6,5) AS "Fruit";
SELECT substr('Watermelon',6,5) AS "Fruit";
﻿
The updated table is shown below:
Output
+-------------+
| Fruit       |
+-------------+
| melon       |
+-------------+
+-------------+
| Fruit       |
+-------------+
| melon       |
+-------------+
﻿
Case 2: SUBSTR() function with length = 0
The following query will extract a string with length = 0:
Select substr
SELECT substr('Watermelon',6,0) AS "Fruit";
SELECT substr('Watermelon',6,0) AS "Fruit";
﻿
It will display an empty output as there is no length specified:
Output
+--------+
| Fruit  |
+--------+
|        |
+--------+
+--------+
| Fruit  |
+--------+
|        |
+--------+
﻿
Case 3: SUBSTR() function with length = negative value
Here we will check if the length is specified with a negative value:
Select substr
SELECT substr('Watermelon',6,-2) AS "Fruit";
SELECT substr('Watermelon',6,-2) AS "Fruit";
﻿
Instead of extracting the string from the last characters, it will return an error as seen below:
Output
ERROR:  negative substring length not allowed
SQL state: 22011
ERROR:  negative substring length not allowed
SQL state: 22011
﻿
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
SELECT substr('Watermelon',20,2) AS "Fruit";
SELECT substr('Watermelon',20,2) AS "Fruit";
﻿
It will display an empty output as shown below:
Output
+--------+
| Fruit  |
+--------+
|        |
+--------+
+--------+
| Fruit  |
+--------+
|        |
+--------+
﻿
﻿

Updated 09 Oct 2023

Did this page help you?

CONCAT

STRPOS

Docs powered by Archbee

TABLE OF CONTENTS

Case 1: SUBSTR() function with specified start_position & length

Case 2: SUBSTR() function with length = 0

Case 3: SUBSTR() function with length = negative value

Case 4: SUBSTR() function with start_position > string

Input	Return
start_position = 1	The count starts from the first character.
start_position < 0 start_position < string	The start_position is set to 1, and the count starts from the first character.
start_position > string	Returns an empty substring.
start_position = negative value	The count starts from the end of the string.

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.