Replace first digit of phone number

Question

I need to transfer the phone_number fields from other tables so that the first digit starts not with 7, but with 8. How can I implement this correctly? For example, numbers of the type 87001059505, and not 77001059505, should be inserted in the table

My try:

SELECT
(PHONE_NUMBER case when phone_number is not null then phone_number := (8+substr(phone_number,2)) end)
FROM CALL_HISTORY

Answer 1

It isn't clear whether you want to view your data this or do an update. Assuming the former, use:

SELECT CASE WHEN phone_number LIKE '7%'
            THEN '8' || SUBSTR(phone_number, 2)
            ELSE phone_number END AS phone_number
FROM CALL_HISTORY;

Assuming the latter, use:

UPDATE CALL_HISTORY
SET phone_number = '8' || SUBSTR(phone_number, 2)
WHERE phone_number LIKE '7%';