如何从SSNPLUS前9位更新USER表中无效SSN字段

需求说明 #

USER表包含SSN和SSNPLUS字段（SSNPLUS为SSN后追加5位数字）。部分行的SSN字段因包含字母损坏，需仅针对这些行，用SSNPLUS字段的前9位更新SSN字段。

示例数据 #

原始数据 #

SSN       SSNPLUS
012345678 01234567899221   <-- 有效数据
888888888 88888888854221   <-- 有效数据
56ABCJ    07512333800998   <-- SSN含字母，无效
992345678 99234567877112   <-- 有效数据
81AJUNK   01526690966772   <-- SSN含字母，无效
098765432 09876543288992   <-- 有效数据

期望结果 #

SSN       SSNPLUS
012345678 01234567899221   
888888888 88888888854221   
075123338 07512333800998   
992345678 99234567877112   
015266909 01526690966772    
098765432 09876543288992   

你的思路伪代码 #

update USER set USER.ssn select left(ssnplus, 9) from USER.ssnplus where ssn contains [A-Z]

解决方案 #

不同数据库的正则匹配语法存在差异，以下是主流数据库的实现方案：

MySQL/MariaDB #

使用REGEXP匹配包含字母的SSN，通过LEFT()截取SSNPLUS前9位：

UPDATE USER
SET SSN = LEFT(SSNPLUS, 9)
WHERE SSN REGEXP '[A-Za-z]';

SQL Server #

使用PATINDEX定位包含字母的行：

UPDATE USER
SET SSN = LEFT(SSNPLUS, 9)
WHERE PATINDEX('%[A-Za-z]%', SSN) > 0;

Oracle #

使用REGEXP_LIKE进行正则匹配，SUBSTR()截取指定长度内容：

UPDATE USER
SET SSN = SUBSTR(SSNPLUS, 1, 9)
WHERE REGEXP_LIKE(SSN, '[A-Za-z]');

重要提示 #

• 执行更新前，建议先运行查询语句验证目标行及更新后的值是否正确：
  以MySQL为例：

  SELECT SSN, SSNPLUS, LEFT(SSNPLUS, 9) AS NEW_SSN
  FROM USER
  WHERE SSN REGEXP '[A-Za-z]';
  

• 若数据库支持事务，建议开启事务后执行更新，确认结果无误再提交，避免误操作导致数据异常。

内容的提问来源于stack exchange，提问作者Twelve-0-Seven