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

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)

Practice Online

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

Previous: 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.