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

See this post https://stackoverflow.com/questions/18625548/select-query-to-remove-non-numeric-characters — , Mar 02 '22 at 11:41
Side point: `+` and `00` are *not* the same thing in telephone numbers. In many phone systems (such as the UK) they have the same *meaning*, but they aren't the same. See also https://github.com/google/libphonenumber/blob/master/FALSEHOODS.md — Charlieface, Mar 02 '22 at 15:13

Stu · Answer 1 · 2022-03-02T11:50:18.790

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.

score 1 · Accepted Answer · answered Mar 02 '22 at 12:48

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

@Kendle I prefere RTRIM & LTRIM for the simple reason that TRIM only got intruduced with Sql Server 2017. — LukStorms, Mar 02 '22 at 13:23

How to update a column supposed to be only numbers

2 Answers2