MS SQL——如何从长的不一致字符串中提取电话号码

Question

我有一项任务要完成，我必须将大约970000个用户从别人的数据库导入我们的数据库。在源数据库中，有一个电话号码字段，其中包含多个连接到一个可怕字符串中的电话号码。

以下是一些数据示例：

|Home: 555-555-5555 Office: (555)-555-5555 Work: 5555555555|
|Home: Office: 555555-5555 Work: 555-555-5555|
|Office: 555-555-5555 Home: [phone] some Comment here|

我遇到的问题是

1. 数字的顺序不一致
2. 到处都是免费的评论
3. 有些电话号码的格式不同

如果可能的话，我真的更喜欢通过SQL来实现这一点，并且我很难用最少的手动调整来以高效的方式实现这一目标。

在我的数据库中，我们为每种电话号码类型都有单独的列，所以我基本上需要将这些字符串拆分为相应的列。

如果我遗漏了什么，请告诉我。

Answer 1

在这段代码中，您将需要额外的3列来存储新的电话号码

这是代码中的逻辑

1. 拆分电话号码

2. 剪切分割结果中前3个最后3个数字之前的文本

3. 删除电话号码中使用的外来字符（仅限样本中使用的字符）

4. 在位置7和4 处插入替换分离器"-"

5. 分组数据

6. 更新表

样本数据：

DECLARE @t table
  (phone varchar(500), home varchar(50), work varchar(50), office varchar(50))
INSERT @t(phone) values
('Home: 555-555-5551 Office: (555)-555-5555 Work: 5555555552|'),
('|Home: Office: 555555-5555 Work: 555-555-5555|'),
('|Office: 555-555-5555 Home: [phone] some Comment here|')

更新：

;WITH CTE as
(
     SELECT
       nid,work, home, office, 
       t.c.value('.', 'VARCHAR(2000)') phone
     FROM (
         SELECT
           row_number() over(order by (select 1)) nid, work, home,office, 
           x = CAST('<t>' + 
               REPLACE(REPLACE(REPLACE(phone, 'Work', '</t><t>work')
               ,'Office', '</t><t>Office'), 'Home', '</t><t>Home')
                + '</t>' AS XML)
        FROM @t -- replace @t with your table
     ) a
     CROSS APPLY x.nodes('/t') t(c)
     WHERE t.c.value('.', 'VARCHAR(2000)') like '%[0-9][0-9][0-9]%'
), CTE2 as
(
SELECT 
work,max(case when phone like '%work%' then z end) over(partition by nid)nwork,
home,max(case when phone like '%home%' then z end) over(partition by nid)nhome,
office,max(case when phone like '%office%' then z end) over(partition by nid)noffice
FROM cte t
CROSS APPLY(SELECT REVERSE(SUBSTRING(phone,PATINDEX('%[0-9][0-9][0-9]%', phone), 20))x)y
CROSS APPLY(SELECT STUFF(STUFF(REPLACE(REPLACE(REPLACE(REVERSE(
  SUBSTRING(x, PATINDEX('%[0-9][0-9][0-9]%', x), 20)), ')', ''), '', ''),
  '-', ''),7,0, '-'),4,0,'-')z)v    )
UPDATE CTE2
SET work = nwork, home = nhome, office = noffice
SELECT home,work,office FROM @t

结果：

home          work          office
555-555-5551  555-555-5552  555-555-5555
NULL          555-555-5555  555-555-5555
555-555-5555  NULL          555-555-5555

Answer 2

使用T-SQL执行此任务不是最佳选择。最接近的解决方案是创建一个CLR程序集，该程序集将利用.NET.中提供的RegEx功能

但是，您也可以查看数据质量服务。它是为这种特定类型的任务（手动条目清理、统一、重复数据消除等）创建的SQL Server组件。但是，它需要SQL Server的BI或Enterprise edtion。