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

CodePudding user response：

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.

CodePudding user response：

You can add a User-Defined Function for this.

For example:

CREATE FUNCTION dbo.fnCleanTel (@Input VARCHAR(255))
RETURNS VARCHAR(255)
AS
BEGIN
  DECLARE @Output VARCHAR(255) = LTRIM(RTRIM(@Input));
  
  IF LEFT(@Output, 1) = ' '
    SET @Output = STUFF(@Output,1,1,'00');
    
  WHILE PATINDEX('%[^0-9]%', @Output) > 0
    SET @Output = REPLACE(@Output, SUBSTRING(@Output,PATINDEX('%[^0-9]%',@Output),1),'');
  
  RETURN @Output;
END;

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

insert into PersonsFinal (tel, firstname, lastname) 
select dbo.fnCleanTel(tel) as clean_tel, firstname, lastname 
from PersonsInitial;

select tel, firstname, lastname from PersonsFinal

tel           | firstname | lastname
:------------ | :-------- | :-------
00412354844   | Manu      | Johns   
01855234      | Fernand   | Wajk    
0041342354844 | Fred      | Johns   

db<>fiddle here