巧用SQL函数解决数据整理难题

      正在开展的宁夏全区保障性安居工程审计中，审计人员采集到了全区享受廉租补贴和实物配租等待遇的所有人员的详细信息，但是由于部分市县区住房保障部门在管理中没有建立信息系统，而只是使用Office软件手工记录保障人员信息，造成所管理的人员信息出现了不准确的现象，如关键字段“身份证号码”一栏中号码有误或者号码后带有文字说明，并且情况复杂不统一。

      为了能够保证数据适合审计人员分析、比对的需要，综合组技术人员决定对不符合要求的数据进行统一清洗和整理。在这次数据清洗和整理过程中，技术人员巧妙使用了SQL语句中的case和replace等函数，方便快捷地解决了上述难题，现将使用方法提供如下，供其他审计人员参考。

      一是，需要审计人员观察、了解原始数据的情况，分清哪些数据可以进行清洗、整理。

      观察发现，原始表中身份证号出现了多个长度，根据常识判断，其中只有18位和15位的身份证号是正确的。继续观察，审计人员发现，其他异常位数的身份证号中，如29位、30位等位数的身份证号是因为号码前后存在空格、某种字符或者中文说明才加长了身份证号长度，并且规律性很强，而4位、6位等长度的身份证号则完全无法推断出正确的身份证号，所以不再关注。

      二是，在判断出哪些数据可以使用并掌握规律后，审计人员编写了下述语句：

      

      清洗掉多余字符：

      select REPLACE（要搜索的字段名，要查找的字符串，替换的字符串）新字段名，*from数据库表

      注意，REPLACE函数中要查找的字符串和替换的字符串的左右需要使用单引号。当然，如果需要清洗的多余字符比较多，可以多次或者嵌套使用REPLACE函数。

      挑选正确或者可以进行清洗的数据使用case函数进行整理：select行政区划名称，廉租补贴人姓名，case

      when（LEN（廉租补贴人身份证号）=23）then SUBSTRING（廉租补贴人身份证号，1，18）

      when（LEN（廉租补贴人身份证号）=29）then SUBSTRING（廉租补贴人身份证号，1，18）

      when（LEN（廉租补贴人身份证号）=30）then SUBSTRING（廉租补贴人身份证号，1，18）

      when（LEN（廉租补贴人身份证号）=40）then SUBSTRING（right（廉租补贴人身份证号，19），1，18）

      when（LEN（廉租补贴人身份证号）=41）then SUBSTRING（right（廉租补贴人身份证号，19），1，18）

      when（LEN（廉租补贴人身份证号）=21）then right（廉租补贴人身份证号，18）

      when（LEN（廉租补贴人身份证号）=22）then right（租补贴人身份证号，18）

      else廉租补贴人身份证号

      as廉租补贴人身份证号，

      银行账号，廉租补贴人配偶姓名，

      when（LEN（廉租补贴人配偶身份证号）=23）then SUBSTRING（廉租补贴人配偶身份证号，1，18）

      when（LEN（廉租补贴人配偶身份证号）=29）then SUBSTRING（廉租补贴人配偶身份证号，1，18）

      when（LEN（廉租补贴人配偶身份证号）=26）then SUBSTRING（廉租补贴人配偶身份证号，1，18）

      when（LEN（廉租补贴人配偶身份证号）=30）then SUBSTRING（廉租补贴人配偶身份证号，1，18）

      when（LEN（廉租补贴人配偶身份证号）=24）then SUBSTRING（廉租补贴人配偶身份证号，1，18）

      when（LEN（廉租补贴人配偶身份证号）=22）then SUBSTRING（廉租补贴人配偶身份证号，1，18）

      when（LEN（廉租补贴人配偶身份证号）=25）then SUBSTRING（廉租补贴人配偶身份证号，1，18）

      when（LEN（廉租补贴人配偶身份证号）=20）then SUBSTRING（廉租补贴人配偶身份证号，1，18）

      when（LEN（廉租补贴人配偶身份证号）=28）then SUBSTRING（廉租补贴人配偶身份证号，1，18）

      when（LEN（廉租补贴人配偶身份证号）=37）then right（廉租补贴人配偶身份证号，18）

      else廉租补贴人配偶身份证号

      

      as廉租补贴人配偶身份证号，

      保障人口数，月补助额，发放年月

      from全区廉租补贴数据汇总

      注意，case函数的基本格式是case when…then…else…end，其中，when的数量就是可以进行清洗的各类数据数量。