背景
在解决数据时,当业务数据同步至MaxCompute后,会产生一些含异样字符的脏数据,比方字段中蕴含了一个不可见字符,在DataWorks中显示不进去,但在BI界面又会显示成其余字符,影响整体观感。这种状况,通常咱们的解法是,将异样的字符洗掉,上面来介绍几种常见的解决异样字符的办法。
问题形容
定位
如下图,能够看到“异样name”和“失常name”的 length值 不同,多了个不可见字符,然而咱们并不能看进去啥。前期做数据处理或数据展现可能成为一个难以定位的问题。
SELECT name as 异样name, LENGTH(name) as 异样name长度, '北京' as 失常name, LENGTH('北京') as 失常name长度from tbl1 where name RLIKE '北京';
后果:
小技巧
- 咱们能够通过在线Unicode编码转换工具,将数值粘贴过来,获取到对应的Unicode码。同理也能够获取其余异样字符的Unicode码,以便后续解决。
- 输出异样 vs 失常的字符串,比照 Unicode 差别能够倒推不可见字符为“ \u200b” 。
解决方案
- 定位到问题后,回顾数据荡涤的惯例计划,想方法把消掉这种不可见字符
计划 | 形容 | 备注 | 本case是否实用 |
---|---|---|---|
trim()函数 | 惯例的首尾不可见字符解决 | 实用首尾部的空格、tab、换行 | Yes |
replace()函数 | 定向剔除字符串 | 实用于单个待替换的字符,多个须要层层嵌套 | Yes |
正则替换函数 | 定向剔除一类字符串 | 通过正则匹配符,替换一类字符串 | Yes |
计划1:trim() - 替换
- MaxCompute的trim()函数反对通过设置参数的形式调特色字符
- TRIM相干文档:https://help.aliyun.com/document_detail/455667.html
成果如下:
- 利用 trim() 函数将数值中的异样不可见字符替换为失常空值字符(不可见字符可通过在线Unicode编码转换工具Unicode转中文复制一下)
SELECT name as 异样name, LENGTH(name) as 异样name长度, trim(name,'') as 失常name, LENGTH(trim(name,'')) as 失常name长度from tbl1 where name RLIKE '北京';
后果:
计划2:replace() - 替换
- 长处:点对点思路解决问题,方便快捷
- 毛病:实用待替换的字符只有一个状况,如果有多个,须要再套一层replace函数,不容易保护
- REPLACE相干文档:https://help.aliyun.com/document_detail/455611.html
成果如下:
- 利用 replace() 函数将数值中的异样不可见字符替换为失常空值字符(不可见字符可通过在线Unicode编码转换工具Unicode转中文复制一下)
SELECT name as 异样name, LENGTH(name) as 异样name长度, replace(name,'','') as 失常name, LENGTH(replace(name,'','')) as 失常name长度from tbl1 where name RLIKE '北京';
后果:
计划4:正则表达式 - 替换
- 应用函数一层一层替换会比拟麻烦,目前MaxCompute反对通过正则表达式匹配出异样的字符
- 只有筛选出了带有异样的一部分字符,再解决就不便很多了
- 例如上文中的不可见字符,Unicode码为“\u200b”,通过执行如下SQL
select id,name from tbl1 where name rlike '[\x{200b}]';
后果返回:
- 此时如果再用regexp_replace()函数能够替换掉这一部分含不可见字符的数值
-- regexp_replace()函数SELECT name as 异样name, LENGTH(name) as 异样name长度, regexp_replace(name, '[\x{200b}]', '',0) as 失常name, LENGTH(regexp_replace(name, '[\x{200b}]', '',0)) as 失常name长度FROM tbl1;
后果:
总结
针对于SQL中的不可见字符或者其余中文等异样字符,都能够应用计划3中的筛选形式筛进去,而后再做后续的替换或其余操作就能够了。
【MaxCompute 已公布收费试用打算,为数仓建设提速】新用户可 0 元支付 5000CU*小时计算资源与 100GB 存储,有效期 3 个月。 立刻支付>>
欢送各位开发者退出大数据计算 MaxCompute 社区。立刻退出