SELECT and REPLACE (clean) data on the fly - SQL

Question

I'm new with SQL. I'm trying to get data from database (Postgres) and replace them on the fly if those are not valid. Is this possible to do this using pure SQL? For example in my DB users I've got the field phone with following data:

|phone           |
------------------
|+79844533324    |
|893233314215    |
|dfgdhf          |
|45              |
|+               |
|                |
|(345)53326784457|
|8(123)346-34-45 |

etc..

I'd like to get only last ten digits if:

1. the phone number starts from 8 or 7 or +7 or +8 .
2. the number contains ten digits after 8 or 7 or +7 or +8 not more and not less

like this:

|phone           |
------------------
|9844533324      |
|1233463445      |

I guess it might be to complicated for SQL. I've researched ton of manuals but most of them cover just SELECT with regex condition.

Answer 1

I think this might work:

select
  right (regexp_replace (phone, '\D', '', 'g'), 10)
from foo
where
  phone ~ '^\+?[78]' and
  regexp_replace (phone, '\D', '', 'g') ~ '^[78]\d{10}$'

In the way of explanation:

right (regexp_replace (phone, '\D', '', 'g'), 10)

Removes all non-digits from the field and then takes the right ten characters -- it will only do this if it meets the following conditions:

phone ~ '^\+?[78]' and

The phone begins with an optional '+' and then a 7 or an 8

regexp_replace (phone, '\D', '', 'g') ~ '^[78]\d{10}$'

And the field, stripped of all non-digits starts with a 7 or 8 followed by exactly ten other digits.

Results:

9844533324
1233463445