PostgreSQL SUBSTRING() function
SUBSTRING() function
The PostgreSQL substring function is used to extract a string containing a specific number of characters from a particular position of a given string.
Syntax:
substring(string [from <str_pos>] [for <ext_char>])
Parameters
Name | Description |
---|---|
string | The main string from where the character to be extracted. |
str_pos | Optional. The position of the string from where the extracting will be starting. If this parameter is omitted, the substring function will start at position 1 (which is the first position in the string). |
ext_char | Optional. A number of characters to be extracted from the string. If this parameter is omitted, the substring function will return the entire string (from the start_position to the end of the string). |
PostgreSQL Version: 9.3
Pictorial Presentation of PostgreSQL SUBSTRING() function

Example: PostgreSQL SUBSTRING() function
The following PostgreSQL statement returns 5 characters starting from the 4th position from the string 'w3resource', that is ‘esour’.
Code:
SELECT substring('w3resource' from 4 for 5);
Sample Output:
substring ----------- esour (1 row)
PostgreSQL SUBSTRING() function using Column :
Sample Table: employees.
If we want to display the employee_id, first name and 1st 4 characters of first_name for those employees who belong to the department which department_id is below 50 from employees table, the following SQL can be executed:
Code:
SELECT employee_id,first_name,
substring(first_name,1,4) "1st 4 characters of first_name"
FROM employees
WHERE department_id<50;
Sample Output:
employee_id | first_name | 1st 4 characters of first_name -------------+------------+-------------------------------- 114 | Den | Den 115 | Alexander | Alex 116 | Shelli | Shel 117 | Sigal | Siga 118 | Guy | Guy 119 | Karen | Kare 178 | Kimberely | Kimb 200 | Jennifer | Jenn 201 | Michael | Mich 202 | Pat | Pat 203 | Susan | Susa (11 rows)
Here is another example:
Sample Table: employees.
If we want to display the employee_id,last name and the position of the substring 'an' in the last_name column for those employees who have a substring 'an' in their last_name column from employees table, the following SQL can be executed :
Code:
SELECT employee_id,last_name,
position('an' in last_name) "Position of 'an'"
FROM employees
WHERE substring(last_name,position('an' in last_name),2)='an';
Sample Output:
employee_id | last_name | Position of 'an' -------------+------------+------------------ 102 | De Haan | 6 112 | Urman | 4 123 | Vollman | 6 127 | Landry | 2 136 | Philtanker | 6 160 | Doran | 4 167 | Banda | 2 178 | Grant | 3 182 | Sullivan | 7 184 | Sarchand | 6 199 | Grant | 3 (11 rows)
Previous: POSITION function
Next: TRIM function
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join