一次gawk gsub函数的实际应用

在做一个数据清洗需求的时候，需要查询两张表里几个字段相同的重复数据。大概思路就是用exists语句，类似：
select *
  from a
 where exists (select 1
          from b
         where a.col1 = b.col1
           and a.col2 = a.col2);
但是这里麻烦的地方在于要匹配的列太多了：
a.INFOCODE,a.SOURCENAME,a.SOURCETYPE,a.PUBLISHTYPE,a.NOTICEDATE,a.ENDDATE,a.NOTICETITLE,a.LANGUAGE,a.IMPORTLEVEL,a.SOURCEURL,a.ATTACHTYPE,a.ATTACHNAME,a.ATTACHSIZE,a.FORM,a.ACCESSORYNUM,a.NOTICESTATE,a.PUBLISHDATE,a.FILENUMBER
用Linux文本处理的方法解决这个问题：
先将这段放到一个文本里：
root@bd-dev-mingshuo-183:/tmp#more 1
a.INFOCODE,a.SOURCENAME,a.SOURCETYPE,a.PUBLISHTYPE,a.NOTICEDATE,a.ENDDATE,a.NOTICETITLE,a.LANGUAGE,a.IMPORTLEVEL,a.SOURCEURL,a.ATTACHTYPE,a.ATTACHNAME,a.ATTACHSIZE,a.FORM,a.ACCESSORYNUM,
a.NOTICESTATE,a.PUBLISHDATE,a.FILENUMBER


这里介绍一下gawk里的gsub函数  
gsub匹配所有的符合正则表达式的内容，然后替换，相当于 sed 's//g'  
语法如下：
gsub(regular expression, subsitution string, target string);
处理的目标范围是第三个字段，匹配条件是第一个参数，匹配后，替换为第二个参数。

将一行文本处理为多行文本：
root@bd-dev-mingshuo-183:/tmp#more 1|gawk 'gsub(/,/,"\n",$0)'
a.INFOCODE
a.SOURCENAME
a.SOURCETYPE
a.PUBLISHTYPE
a.NOTICEDATE
a.ENDDATE
a.NOTICETITLE
a.LANGUAGE
a.IMPORTLEVEL
a.SOURCEURL
a.ATTACHTYPE
a.ATTACHNAME
a.ATTACHSIZE
a.FORM
a.ACCESSORYNUM
a.NOTICESTATE
a.PUBLISHDATE
a.FILENUMBER


复制每一列：
root@bd-dev-mingshuo-183:/tmp#more 1|gawk 'gsub(/,/,"\n",$0)'|gawk -F'\n' '{print "on",$0,"=",$0,"and"}'
on a.INFOCODE = a.INFOCODE and
on a.SOURCENAME = a.SOURCENAME and
on a.SOURCETYPE = a.SOURCETYPE and
on a.PUBLISHTYPE = a.PUBLISHTYPE and
on a.NOTICEDATE = a.NOTICEDATE and
on a.ENDDATE = a.ENDDATE and
on a.NOTICETITLE = a.NOTICETITLE and
on a.LANGUAGE = a.LANGUAGE and
on a.IMPORTLEVEL = a.IMPORTLEVEL and
on a.SOURCEURL = a.SOURCEURL and
on a.ATTACHTYPE = a.ATTACHTYPE and
on a.ATTACHNAME = a.ATTACHNAME and
on a.ATTACHSIZE = a.ATTACHSIZE and
on a.FORM = a.FORM and
on a.ACCESSORYNUM = a.ACCESSORYNUM and
on a.NOTICESTATE = a.NOTICESTATE and
on a.PUBLISHDATE = a.PUBLISHDATE and
on a.FILENUMBER = a.FILENUMBER and

替换
root@bd-dev-mingshuo-183:/tmp#more 1|gawk 'gsub(/,/,"\n",$0)'|gawk -F'\n' '{print $0,"=",$0,"and"}'|sed 's/= a/= b/g'     
a.INFOCODE = b.INFOCODE and
a.SOURCENAME = b.SOURCENAME and
a.SOURCETYPE = b.SOURCETYPE and
a.PUBLISHTYPE = b.PUBLISHTYPE and
a.NOTICEDATE = b.NOTICEDATE and
a.ENDDATE = b.ENDDATE and
a.NOTICETITLE = b.NOTICETITLE and
a.LANGUAGE = b.LANGUAGE and
a.IMPORTLEVEL = b.IMPORTLEVEL and
a.SOURCEURL = b.SOURCEURL and
a.ATTACHTYPE = b.ATTACHTYPE and
a.ATTACHNAME = b.ATTACHNAME and
a.ATTACHSIZE = b.ATTACHSIZE and
a.FORM = b.FORM and
a.ACCESSORYNUM = b.ACCESSORYNUM and
a.NOTICESTATE = b.NOTICESTATE and
a.PUBLISHDATE = b.PUBLISHDATE and
a.FILENUMBER = b.FILENUMBER and

这样就得到了目标文本。黏贴到sql中，执行就好了。
处理过程比较简单，重点在于gawk里的gsub函数的应用，以及处理思路。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31480688/viewspace-2218217/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31480688/viewspace-2218217/