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:

UPDATE employees 
SET phone_number = REPLACE(phone_number, '124', '999') 
WHERE phone_number LIKE '%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.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming