w3resource

PostgreSQL String() Function: Join a particular text with a column


5. Write a query to join the text '@example.com' with the email column.

Sample Output :

  EMAIL
  --------------------
  [email protected]
  [email protected]
  [email protected]
  [email protected]
  [email protected]
  [email protected]
  [email protected]
  [email protected]
  [email protected]
  - - - -  - - - - - - -
  - - - -  - - - - - - -

Sample Solution:

Code:

-- This SQL statement updates the email addresses of employees by appending '@example.com' to their existing email addresses.

UPDATE employees -- Specifies the table to be updated, in this case, the employees table
SET email = CONCAT(email, '@example.com'); -- Updates the email column by concatenating the existing email addresses with '@example.com'

Explanation:

  • This SQL statement updates the email addresses of employees by appending '@example.com' to their existing email addresses.
  • The UPDATE statement modifies existing records in the specified table.
  • The employees table is specified as the table to be updated.
  • The SET clause indicates the column(s) to be updated and the new value(s) to be assigned.
  • The email column of the employees table is updated by concatenating the existing email addresses with the string '@example.com'.
  • The CONCAT() function is used to concatenate the existing email addresses with the string '@example.com'.
  • After execution, each employee's email address will be updated to include '@example.com' at the end.

Sample table: employees


Output:

postgres=# UPDATE employees SET email =
postgres-# CONCAT(email, '@example.com');
UPDATE 107

Here is the command to see the updated result:

postgres=# SELECT *  from emp;
 employee_id | first_name  |  last_name  |           email           |    phone_number    |
-------------+-------------+-------------+---------------------------+--------------------+-
         100 | Steven      | King        | [email protected]         | 515.123.4567       |
         101 | Neena       | Kochhar     | [email protected]      | 515.123.4568       |
         102 | Lex         | De Haan     | [email protected]       | 515.123.4569       |
         103 | Alexander   | Hunold      | [email protected]       | 590.423.4567       |
         104 | Bruce       | Ernst       | [email protected]        | 590.423.4568       |
         105 | David       | Austin      | [email protected]       | 590.423.4569       |
         106 | Valli       | Pataballa   | [email protected]      | 590.423.4560       |
         107 | Diana       | Lorentz     | [email protected]      | 590.423.5567       |
         114 | Den         | Raphaely    | [email protected]      | 515.127.4561       |
         115 | Alexander   | Khoo        | [email protected]         | 515.127.4562       |
         116 | Shelli      | Baida       | [email protected]        | 515.127.4563       |
         117 | Sigal       | Tobias      | [email protected]       | 515.127.4564       |
         118 | Guy         | Himuro      | [email protected]       | 515.127.4565       |
         119 | Karen       | Colmenares  | [email protected]      | 515.127.4566       |
         120 | Matthew     | Weiss       | [email protected]        | 650.123.1234       |
         ...

N.B.: A few columns have been displayed as a sample result.

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

Previous: Write a query to fill the maximum and minimum salary with leading asterisks whether these two columns does not contain a seven digit number.
Next: Write a query to get the employee id, first name and hire month of an employee.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.