ASCII码特殊控制字符替换

Viewed 12
query

如图，JSON中有特殊字符，在Doris中执行如下SQL报错非法JSON

select json_parse('{"MACHINE_NO":"","TRAY":"1","OPERATORID":"","FN_LX":"7.3075","CAP_LX":"13.96125","_LX":"3.1525"}');

image.png

显示为DC2的是ASCII中特殊控制字符

ndong26
1 Answers

使用regexp_replace函数统一替换下特殊字符(ASCII 码中的1~31)即可：

with c as
(
select '{"MACHINE_NO":"","TRAY":"1","OPERATORID":"","FN_LX":"7.3075","CAP_LX":"13.96125","_LX":"3.1525"}' as str

)
SELECT regexp_replace(str, '[\\x00-\\x1F]', '') AS replace_str
, json_parse(regexp_replace(str, '[\\x00-\\x1F]', ''))
from c

image.png

ndong26