PostgreSQL SUBSTRING() function

Last update on November 13 2023 09:59:01 (UTC/GMT +8 hours)

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

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