WITH MYTAB (NUM) AS
(
VALUES
  'Т.+79111111111'
, '89111111111 Это у Васи'
, '8(912)1111111'
, '89111111111'
, '79222222222'
, '8-912-1111111'
, '223322 Это у Васи'
)
SELECT 
  NUM
, CASE WHEN SUBSTR(NUM_NEW, 1, 2) = '79' AND LENGTH(NUM_NEW) = 11 THEN NUM_NEW ELSE NUM END NUM_NEW
FROM
(
SELECT 
  NUM
, xmlcast
(
  xmlquery
  ('fn:replace($s, "^8", "7")' 
    passing 
      xmlcast(xmlquery('fn:replace($s, "[^\d]", "")' passing T.NUM  as "s") as varchar(4000))  
    as "s"
  ) as varchar(4000)
) NUM_NEW
FROM MYTAB T
) T;

|NUM                   |NUM_NEW          |
|----------------------|-----------------|
|Т.+79111111111        |79111111111      |
|89111111111 Это у Васи|79111111111      |
|8(912)1111111         |79121111111      |
|89111111111           |79111111111      |
|79222222222           |79222222222      |
|8-912-1111111         |79121111111      |
|223322 Это у Васи     |223322 Это у Васи|