(本文档仅供参考)
问题说明
SQL在数据库软件、测试jsp中是运行正常的,但是在smartbi中却报“表达式解析错误... ”。
经常存在多个字符拼接在一起的场景。多个特殊字符需要进行拼接,如oracle可以使用||拼接,mysql可以使用concat函数拼接
Oracle:select to_number(regexp_substr('20180721',chr(91)||'0-9'||chr(93)||'*'||chr(91)||'0-9'||chr(93),1)) from dual
mysql:select cast(concat('a',char(92)) as char) from dual
解决方案
Oracle转义示例
select chr(92)||'1XXXX' from dual; --正则表达式转义 select chr(94)||chr(92)||'d{10}$' from dual select chr(91)||'0-9'||chr(93)||'*'||chr(91)||'0-9'||chr(93) as a from dual select to_number(regexp_substr('20180721',chr(91)||'0-9'||chr(93)||'*'||chr(91)||'0-9'||chr(93),1)) from dual; SELECT REPLACE(CHR(10),'\n') FROM dual SELECT REPLACE(CHR(10),concat(chr(92),'n')) as a FROM dual
postgresql转义拼接示例
select cast(concat(char(91),'123',char(93),'hao',char(58))as char) as a select concat('a','123','hao') as a select substring('[123]hao:','^\[.+\](.+):'); select substring('[123]hao:',chr(94) || E'\\[.+\\](.+):');