Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

PostgreSQL SUBSTRING() function

SUBSTRING() function

The PostgreSQL substring function is used to extract a string containing a specific number of characters from a particular position of a given string.

Syntax:

substring(string [from <str_pos>] [for <ext_char>])

Parameters

Name Description
string The main string from where the character to be extracted.
str_pos Optional. The position of the string from where the extracting will be starting. If this parameter is omitted, the substring function will start at position 1 (which is the first position in the string).
ext_char Optional. A number of characters to be extracted from the string. If this parameter is omitted, the substring function will return the entire string (from the start_position to the end of the string).

PostgreSQL Version: 9.3

Pictorial Presentation of PostgreSQL SUBSTRING() function

Pictorial presentation of postgresql substring function

Example: PostgreSQL SUBSTRING() function

The following PostgreSQL statement returns 5 characters starting from the 4th position from the string 'w3resource', that is ‘esour’.

Code:

SELECT substring('w3resource' from 4 for 5);

Sample Output: 

 substring
-----------
 esour
(1 row)

PostgreSQL SUBSTRING() function using Column :

Sample Table: employees.


If we want to display the employee_id, first name and 1st 4 characters of first_name for those employees who belong to the department which department_id is below 50 from employees table, the following SQL can be executed:

Code:

SELECT employee_id,first_name,
substring(first_name,1,4) "1st 4 characters of first_name"
FROM employees
WHERE department_id<50;

Sample Output: 

 employee_id | first_name | 1st 4 characters of first_name
-------------+------------+--------------------------------
         114 | Den        | Den
         115 | Alexander  | Alex
         116 | Shelli     | Shel
         117 | Sigal      | Siga
         118 | Guy        | Guy
         119 | Karen      | Kare
         178 | Kimberely  | Kimb
         200 | Jennifer   | Jenn
         201 | Michael    | Mich
         202 | Pat        | Pat
         203 | Susan      | Susa
(11 rows)

Here is another example:

Sample Table: employees.


If we want to display the employee_id,last name and the position of the substring 'an' in the last_name column for those employees who have a substring 'an' in their last_name column from employees table, the following SQL can be executed :

Code:

SELECT employee_id,last_name,
position('an' in last_name) "Position of 'an'"
FROM employees 
WHERE substring(last_name,position('an' in last_name),2)='an';

Sample Output: 

 employee_id | last_name  | Position of 'an'
-------------+------------+------------------
         102 | De Haan    |                6
         112 | Urman      |                4
         123 | Vollman    |                6
         127 | Landry     |                2
         136 | Philtanker |                6
         160 | Doran      |                4
         167 | Banda      |                2
         178 | Grant      |                3
         182 | Sullivan   |                7
         184 | Sarchand   |                6
         199 | Grant      |                3
(11 rows)

Previous: POSITION function
Next: TRIM function