PostgreSQL String() Function: Update the phone_number with '999' where the substring '124' found

2. Write a query to update the phone_number column with '999' where the substring '124' found in that column.

Sample Solution:

Code:

-- Updating the employees table
UPDATE employees 
-- Setting the phone_number column to replace '124' with '999' where phone_number contains '124'
SET phone_number = REPLACE(phone_number, '124', '999') 
-- Applying the update only to rows where phone_number contains '124'
WHERE phone_number LIKE '%124%';

Explanation:

• This SQL query updates the phone_number column in the employees table.
• It uses the REPLACE function to replace occurrences of '124' with '999' in the phone_number column.
• The WHERE clause ensures that the update is only applied to rows where the phone_number contains '124'.

Sample table: employees

Output:

postgres=# UPDATE employees SET phone_number =
postgres-# REPLACE(phone_number, '124', '999')
postgres-# WHERE phone_number LIKE '%124%';
UPDATE 14

Here is the command to see the updated result :

postgres=# SELECT *
postgres-# FROM employees
postgres-# WHERE phone_number LIKE '%999%';
 employee_id | first_name  |  last_name  |  email   | phone_number | hire_date  |   job_id   |  salary  |
-------------+-------------+-------------+----------+--------------+------------+------------+----------+-
         108 | Nancy       | Greenberg   | NGREENBE | 515.999.4569 | 1987-06-25 | FI_MGR     | 12000.00 |
         109 | Daniel      | Faviet      | DFAVIET  | 515.999.4169 | 1987-06-26 | FI_ACCOUNT |  9000.00 |
         110 | John        | Chen        | JCHEN    | 515.999.4269 | 1987-06-27 | FI_ACCOUNT |  8200.00 |
         111 | Ismael      | Sciarra     | ISCIARRA | 515.999.4369 | 1987-06-28 | FI_ACCOUNT |  7700.00 |
         112 | Jose Manuel | Urman       | JMURMAN  | 515.999.4469 | 1987-06-29 | FI_ACCOUNT |  7800.00 |
         113 | Luis        | Popp        | LPOPP    | 515.999.4567 | 1987-06-30 | FI_ACCOUNT |  6900.00 |
         125 | Julia       | Nayer       | JNAYER   | 650.999.1214 | 1987-07-12 | ST_CLERK   |  3200.00 |
         126 | Irene       | Mikkilineni | IMIKKILI | 650.999.1224 | 1987-07-13 | ST_CLERK   |  2700.00 |
         127 | James       | Landry      | JLANDRY  | 650.999.1334 | 1987-07-14 | ST_CLERK   |  2400.00 |
         128 | Steven      | Markle      | SMARKLE  | 650.999.1434 | 1987-07-15 | ST_CLERK   |  2200.00 |
         129 | Laura       | Bissot      | LBISSOT  | 650.999.5234 | 1987-07-16 | ST_CLERK   |  3300.00 |
         130 | Mozhe       | Atkinson    | MATKINSO | 650.999.6234 | 1987-07-17 | ST_CLERK   |  2800.00 |
         131 | James       | Marlow      | JAMRLOW  | 650.999.7234 | 1987-07-18 | ST_CLERK   |  2500.00 |
         132 | TJ          | Olson       | TJOLSON  | 650.999.8234 | 1987-07-19 | ST_CLERK   |  2100.00 |
(14 rows)

N.B.: Some columns have been displayed.

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

Previous: Write a query to get the job_id and the ID(s) for those employees who is working in that post.
Next: Write a query to find the details of those employees who contain eight or more characters in their first name.