PostgreSQL SUBSTR() function
SUBSTR() function
The PostgreSQL substr() function is used to extract a specific number of characters from a particular position of a string.
Uses of SUBSTR() Function
- Extract Substrings: Retrieve a portion of a string starting from a specified position.
- Data Transformation: Manipulate strings for formatting or reformatting purposes.
- String Analysis: Extract meaningful parts of data fields for analysis.
- Conditional Queries: Filter rows based on the extracted substring.
- Dynamic Data Retrieval: Obtain variable-length substrings from columns based on specific criteria.
Syntax:
substr(<string>,<position_from > [,<number_of_characters>]
Parameters:
| Name | Description | Return Type |
|---|---|---|
| string | A string, in which the search will occur. | text |
| position_from | The starting position of search from the string. | integer |
| number_of_characters | A substring which may be one or more characters will be extracted from the string. | text |
PostgreSQL Version: 9.3
Visual Presentation of PostgreSQL SUBSTR() function
Example: PostgreSQL SUBSTR() function:
In the example below, three characters from the second position of the string 'w3resource' have been extracted.
SQL Code:
SELECT substr('w3resource',2,3) AS "Extracting characters";
Output:
Extracting characters ----------------------- 3re (1 row)
PostgreSQL SUBSTR() function using column:
Sample Table: employees.
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------
100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90
101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP | 17000.00 | 0.00 | 100 | 90
102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 | AD_VP | 17000.00 | 0.00 | 100 | 90
103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 1987-06-20 | IT_PROG | 9000.00 | 0.00 | 102 | 60
104 | Bruce | Ernst | BERNST | 590.423.4568 | 1987-06-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60
105 | David | Austin | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 4800.00 | 0.00 | 103 | 60
106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 1987-06-23 | IT_PROG | 4800.00 | 0.00 | 103 | 60
107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 1987-06-24 | IT_PROG | 4200.00 | 0.00 | 103 | 60
108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 1987-06-25 | FI_MGR | 12000.00 | 0.00 | 101 | 100
109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 1987-06-26 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100
..................
206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110
If we want to display the first_name, job_id, and the extraction of three characters from the second position of first_name column from employees table for those employees who drawn the salary of more than 12000, the following SQL can be used.
SQL Code:
SELECT first_name,job_id,
substr(first_name,2,3) AS "Extracting characters"
FROM employees
WHERE salary>12000;
Output:
first_name | job_id | Extracting characters ------------+---------+----------------------- Steven | AD_PRES | tev Neena | AD_VP | een Lex | AD_VP | ex John | SA_MAN | ohn Karen | SA_MAN | are Michael | MK_MAN | ich (6 rows)
PREV : STRPOS function
NEXT : TRANSLATE function