T-SQL:解析名称以忽略空间和中间缩写

Question

我的数据库维护较差，其中包括员工信息。人力资源要求提供一份报告，列出了与保险范围相关的员工名称与保险单中的名称不符的实例。

两个表格中名称的格式都存在不一致之处。它总是姓氏，然后是名字，但是您可能会在任何一个表中看到以下任何一个名为Steven J. Smith的虚构员工：

1. 史密斯，史蒂文
2. 史密斯，史蒂文
3. 史密斯，史蒂文J。
4. 史密斯，史蒂文J。

我需要运行查询查询，以查找员工.emp_name＆lt;> Insurance.subscriber_name的实例，同时允许上面显示的名称格式差异（即，拾取" Smith，Steven J."one_answers" Smith J."one_answers" Smith，Smith，Steven，Steven"，是（可能是）同一个人，并在他们身上igon）。

SELECT 
  EMPLOYEE.EMP_NO
, EMPLOYEE.EMP_NAME
, INSURANCE.SUBSCRIBER_NAME
, INSURANCE.PAYOR_NAME
FROM EMPLOYEE
     INNER JOIN INSURANCE ON EMPLOYEE.EMP_NO = INSURANCE.EMP_NO
WHERE EMPLOYEE.EMP_NAME <> INSURANCE.SUBSCRIBER_NAME

我知道我想做一个子字符串以忽略中间的首字母，但是我如何解释逗号之后是否有空间？

Answer 1

为什么不替换所有逗号和空格？

WHERE REPLACE(REPLACE(EMPLOYEE.EMP_NAME,' ',''),',','') <> REPLACE(REPLACE(INSURANCE.SUBSCRIBER_NAME,' ',''),',','')

Answer 2

您可以简单地 replace out comma

 WHERE replace (EMPLOYEE.EMP_NAME,',','') <> replace (INSURANCE.SUBSCRIBER_NAME,',','')

找到大多数不匹配...

;with cE as 
(select 
     EMP_NO, 
     REPLACE(REPLACE(REPLACE(EMP_NAME,',',''),' ',''),'.','') as namekey 
from EMPLOYEE),
ci as 
(select 
     EMP_NO, 
     REPLACE(REPLACE(REPLACE(SUBSCRIBER_NAME,',',''),' ',''),'.','') as namekey 
from INSURANCE)
select *
from ce
    inner join ci on ce.EMP_NO = ci.EMP_NO
where
     not
     (
    (LEN(ce.namekey)< LEN(ci.namekey) and ci.namekey like ce.namekey+'%')
        or
    (LEN(ce.namekey)>= LEN(ci.namekey) and ce.namekey like ci.namekey+'%')
     )

Answer 3

您可以在逗号后删除空间，然后删除缩写

declare @Temp table (Name nvarchar(128))
insert into @Temp
select 'Smith, Steven' union all
select 'Smith,Steven' union all
select 'Smith, Steven J.' union all
select 'Smith,Steven J.'
select 
    case
        when N1.Name like '% %' then left(N1.Name, charindex(' ', N1.Name))
        else N1.Name
    end as Name_New,
    T.Name
from @Temp as T
    outer apply (select replace(T.Name, ', ', ',') as Name) as N1

Answer 4

谢谢，您的答案对您有很大帮助。我最终将名称切成[lastname] [firstName]没有空间，并在中间的初始字母中切断。这是最终在消除绝大多数同名比赛中起作用的方法：

((CASE
WHEN CHARINDEX(' ',REPLACE(REPLACE(EMPLOYEE.EMP_NAME,', ',''),',','')) = 0
THEN UPPER(REPLACE(REPLACE(EMPLOYEE.EMP_NAME,', ',''),',',''))
ELSE UPPER(LEFT(REPLACE(REPLACE(EMPLOYEE.EMP_NAME,', ',''),',',''),CHARINDEX(' ',REPLACE(REPLACE(EMPLOYEE.EMP_NAME,', ',''),',',''))))
END) <> 
(CASE
WHEN CHARINDEX(' ',REPLACE(REPLACE(INSURANCE.SUBSCRIBER_NAME
,', ',''),',','')) = 0
THEN UPPER(REPLACE(REPLACE(INSURANCE.SUBSCRIBER_NAME
,', ',''),',',''))
ELSE UPPER(LEFT(REPLACE(REPLACE(INSURANCE.SUBSCRIBER_NAME
,', ',''),',',''),CHARINDEX(' ',REPLACE(REPLACE(INSURANCE.SUBSCRIBER_NAME
,', ',''),',',''))))
END))