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:

PostgreSQL Version: 9.3

Pictorial 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.

Code:

SELECT substr('w3resource',2,3) AS "Extracting characters";

Sample 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.

Code:

SELECT first_name,job_id, 
substr(first_name,2,3) AS "Extracting characters" 
FROM employees 
WHERE salary>12000;

Sample 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