How to update a column supposed to be only numbers

Question

I want to transform the values of a column in my table which is supposed to contain only numbers. It is the "TEL" column. This column currently has special characters and spaces. I need to filter this.

The additional constraint is that if the very first character (only the first character) is a "+" I would have to transform the + into "00"

You will find below an example of what is expected.

Could you please help me to create such a query?

CREATE TABLE PersonsInitial (
    tel varchar(255),
    firstname varchar(255),
    lastname varchar(255)
);

insert into PersonsInitial(tel,firstname,lastname) values
('+41/ jfakl2 eaf3efa54844','Manu','Johns'),
('01-afe fa-e8fa5a +e5+ e+234','Fernand','Wajk'),
(' +41/34 jfakl2 eaf3efa54844','Fred','Johns')

;

select tel, firstname, lastname from PersonsInitial

--if there is a person with the same tel number chose the customer id with 'C'
--if I don't have the choice add the customer without C

CREATE TABLE PersonsFinal (
    tel varchar(255),
    firstname varchar(255),
    lastname varchar(255))
;

insert into PersonsFinal(tel,firstname,lastname) values
('00412354844','Manu','Johns'),
('01855234','Fernand','Wajk'),
('0041342354844','Fred','Johns')

;
select tel, firstname, lastname from PersonsFinal

Example

Answer 1

This is something that translate is useful for. You can define a string of characters to remove and replace them all with a single character also to be removed, eg a space, with some additional logic for your initial 00 criteria:

declare @replace varchar(30)='abcdefghijklmnopqrstuvwxyz/+-';

select *, 
  Replace(Translate(Iif(Left(Replace(tel,' ',''),1)='+',Concat('00',tel),tel), @replace, Replicate(' ',Len(@replace))),' ','')
from PersonsInitial;

You can then use an Updatable CTE to fix the original data, see example in Amended DBFiddle

I'd suggest once you have fixed your data you use a Check Constraint to ensure only valid data can be used.