PostgreSQL String() Function: Get the employee id, email id to discard the last three characters

Last update on October 27 2025 12:16:01 (UTC/GMT +8 hours)

7. Write a query to get the employee id, email id to discard the last three characters.

Sample Solution:

Code:

-- This SQL query retrieves employee ID and extracts the email ID from the reversed email addresses.

SELECT employee_id, -- Selects the employee_id column
REVERSE(SUBSTR(REVERSE(email), 4)) as Email_ID -- Reverses the email address, extracts the email ID, and reverses it back
FROM employees; -- Specifies the table from which to retrieve data, in this case, the employees table

Explanation:

This SQL query retrieves employee ID and extracts the email ID from the reversed email addresses.
The SELECT statement selects the employee_id column and the extracted email ID.
The REVERSE() function reverses the characters in the email address.
The SUBSTR() function extracts a substring from the reversed email address, starting from the 4th character.
The REVERSE() function is used again to reverse the extracted email ID back to its original order.
The extracted email ID is labeled as Email_ID using the AS keyword.
The result set will contain the employee ID along with the extracted email ID for each employee in the employees table.

Sample table: employees

Output:

postgres=# SELECT employee_id, SUBSTR(REVERSE(email), 4) as Email_ID
postgres-# FROM employees;
 employee_id | email_id
-------------+----------
         100 | KS
         101 | HCOKN
         102 | HEDL
         103 | NUHA
         104 | REB
         105 | SUAD
         106 | ATAPV
         107 | EROLD
         108 | EERGN
         109 | VAFD
         110 | CJ
         111 | AICSI
         112 | RUMJ
         113 | PL
         114 | HPARD
         115 | KA
         116 | ABS
         117 | BOTS
         118 | MIHG
         119 | MLOCK
         120 | EWM
         121 | RFA
         122 | FUAKP
         123 | LLOVS
         124 | RUOMK
         125 | ANJ
         126 | KKIMI
         127 | NALJ
		...  | ...
         197 | EEFK
         198 | NOCOD
         199 | RGD
         200 | AHWJ
         201 | TRAHM
         202 | P
         203 | VAMS
         204 | BH
         205 | GGIHS
         206 | IGW
(107 rows)

Go to:

PREV : Write a query to get the employee id, first name and hire month of an employee.
NEXT : Write a query to find all the employees which first name contains all the uppercase letter.

Practice Online

Have another way to solve this solution? Contribute your code (and comments) through Disqus.