w3resource

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.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.