PostgreSQL SUBSTR Function

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

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.

Syntax :

substr(<string>,<position_from > [,<number_of_characters>]


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

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.

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

Output :

 Extracting characters
(1 row)

PostgreSQL SUBSTR() function using column :

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.

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)