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
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)
Previous: STRPOS function
Next: TRANSLATE function