如何更新假定仅为数字的列

Question

我想转换表中列的值，该列应该只包含数字。这是“电话”栏目。该列当前具有特殊的字符和空格。我要过滤这个。

附加的约束是，如果第一个字符(只有第一个字符)是"+“，则必须将+转换为"00”。

您将在下面找到一个期望的示例。

你能帮我创建这样的查询吗？

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

示例

Answer 1

这是translate有用的东西。您可以定义一个字符串来删除所有字符，并将它们替换为一个也要删除的字符(如空格)，并为您的初始00条件添加一些附加逻辑：

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

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

然后可以使用可更新的CTE修复原始数据，参见修正后的DBFiddle中的示例。

我建议，一旦您修复了您的数据，您将使用一个Check约束来确保只能使用有效的数据。