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.


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

Pictorial presentation of PostgreSQL LPAD() 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";

Sample Output:

 Extracting characters
(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.


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

Follow us on Facebook and Twitter for latest update.