用MSSQL数据库清理那些乱七八糟的数据，工作效率能蹭蹭往上涨

（开头直接进入主题，不绕弯子） 这事儿我可有发言权，前阵子我们公司系统里那数据，简直没法看，光一个客户姓名，就能给你整出十几种花样：“张三”、“张三（已离职）”、“张三_待核实”、“zhangsan”……财务那边对个账都想骂人，后来我们下决心用MSSQL数据库自带的工具和语句收拾了一遍，那感觉，就像给乱糟糟的仓库来了个大扫除，东西在哪一目了然，干活儿都快多了，下面我就捞干的说，具体是怎么弄的。

先别急着删，把“乱七八糟”的底细摸清楚

（引用一位有十年经验的DBA老王的建议）“清理数据最怕蛮干，你以为没用的数据，可能哪个偏僻的报表还在用，一删就出大事。”所以第一步不是写删除语句，而是用查询把问题揪出来。

1. 用SELECT和WHERE当“放大镜”：乱七八糟的数据往往有特征，查找无效邮箱，可以这么查：SELECT * FROM 客户表 WHERE Email NOT LIKE '%@%.%'，这一下就能把那些没“@”符号或者格式明显不对的邮箱全筛出来，再比如，找重复的客户，可以用GROUP BY和HAVING：SELECT 姓名, 身份证号, COUNT(*) FROM 客户表 GROUP BY 姓名, 身份证号 HAVING COUNT(*) > 1，跑一下，所有重复条目就现原形了。

   

2. 用LEN()和PATINDEX()抓“妖怪”：有些数据表面正常，细看要命，比如地址字段里混进了乱码，或者姓名里有多余的空格、换行符，用LEN(字段名)查长度异常的数据（比如别人的姓名都两三个字，突然有个100多字符的，肯定有问题），用PATINDEX('%[^a-zA-Z0-9一-龥]%', 姓名)可以找出包含非中英文和数字的“脏”数据，这些函数就像探针，能把藏在正常数据里的“妖怪”抓出来。

动手清理，MSSQL的“清洁工具箱”真好用

摸清问题后,就该动手了，MSSQL提供了不少好用的工具来清理。

1. “一键美化”：UPDATE搭配字符串函数：这是最常用的，统一把姓名首字母大写，去掉前后空格：UPDATE 客户表 SET 姓名 = UPPER(LEFT(姓名, 1)) + LOWER(SUBSTRING(姓名, 2, LEN(姓名)))，然后再UPDATE 客户表 SET 姓名 = LTRIM(RTRIM(姓名))，对于地址里“省”、“市”、“区”不统一的，可以用REPLACE()函数批量替换，比如把所有“北京市”统一成“北京”。（这里参考了网上技术社区“博客园”里一篇关于数据清洗的常见SQL技巧总结）

2. “精准排雷”：DELETE与JOIN联手除重复：找到重复数据后，不能简单DELETE，得留一条，这时候可以用到ROW_NUMBER()这个窗口函数，先给重复的数据组内部编号：SELECT *, ROW_NUMBER() OVER (PARTITION BY 身份证号 ORDER BY 创建时间 DESC) as rn FROM 客户表，这样每个重复组里最新的一条就是rn=1，通过一个子查询，DELETE FROM 客户表 WHERE ID IN (SELECT ID FROM ... WHERE rn > 1)，就能精准地只删除旧的重复项，保留最新的一条，这个操作一定要在事务里做，后面会说到。

3. “废物利用”：CASE WHEN智能分类：有些数据不是没用，而是放错了地方或者格式不对，有个备注字段，里面有些人填了手机号，有些人填了邮箱，乱成一锅粥，可以用CASE WHEN表达式配合模式匹配，把它们分离出来：UPDATE 表 SET 手机号 = CASE WHEN 备注 LIKE '%1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' THEN ... END，虽然规则复杂点，但一旦写好，就能把“垃圾”变废为宝。

   

安全第一，让“撤销键”随时在手

（再次引用DBA老王的血泪教训）“没备份就改数据，等于蒙着眼走悬崖。”效率再高，把数据搞丢了就全完了，MSSQL里保证安全有几招：

1. 万能的BEGIN TRANSACTION / ROLLBACK：这是最重要的习惯，在执行任何不确定的UPDATE或DELETE前，先敲BEGIN TRANSACTION，然后执行你的语句，接着马上用SELECT检查结果对不对，如果发现搞砸了，直接ROLLBACK，所有改动就撤销了，数据恢复原样，确认无误后，再COMMIT提交，这就跟你写文档时不停按Ctrl+S一样，是保命的招数。

2. 先SELECT再UPDATE：把要改的数据先用SELECT语句查出来看一遍，确认无误后，再把SELECT *换成UPDATE，条件和字段不变，这能最大程度避免误操作。

3. 用好临时表和备份：对于特别复杂的清理，可以先把原始数据SELECT INTO #临时表复制一份到临时表，在临时表上做各种测试和清理，成功后再用临时表的数据去更新正式表，操作前对整个数据库做个完整备份，是最终的保险。

总结一下真实感受） 说实话，刚开始学用这些MSSQL语句时会觉得有点麻烦，不如手动在Excel里筛选来得“直观”，但一旦熟练了，那种效率的提升是实实在在的，以前要几个人核对一下午的重复客户，现在一段脚本跑一分钟就搞定；以前看到格式乱七八糟的地址就头疼，现在一个批处理就能规整得明明白白，数据干净了，后面做报表、做分析，心里都有底，出错也少了，这么一来二去，工作效率可不是就蹭蹭往上走了嘛，关键就三点：细心观察、大胆使用工具、时刻不忘安全。