sql - 转义 JSON 输出的特殊字符

Question

我有一列包含我想转义的数据以便将其用作 JSON 输出，更准确地说，我试图转义此处列出的相同字符，但使用 Oracle 11g:Special Characters and JSON Escaping Rules

我认为可以使用 REGEXP_REPLACE 解决:

SELECT REGEXP_REPLACE(my_column, '("|\\|/)|(' || CHR(9) || ')', '\\\1') FROM my_table;

但是我对替换其他字符(制表符、换行符、退格符等)感到迷茫，在前面的示例中，我知道\1 将匹配并替换第一组，但我不确定如何捕获第二组中的制表符然后用\t替换它。有人可以给我一个关于如何进行更换的提示吗？

我知道我可以这样做:

SELECT REGEXP_REPLACE( REGEXP_REPLACE(my_column, '("|\\|/)', '\\\1'), '(' || CHR(9) || ')', '\t') 
FROM my_table;

但是我必须嵌套 5 个对 REGEXP_REPLACE 的调用，而且我怀疑我应该能够在一两个调用中完成它。

我知道 JSON 的其他包或库，但我认为这种情况很简单，可以使用 Oracle 提供的开箱即用功能来解决。

谢谢你。

Answer 1

这是一个开始。替换所有常规字符很容易，控制字符会很棘手。此方法使用由字符类组成的组，其中包含要在前面添加反斜杠的字符。请注意，类内的字符不需要转义。 REGEXP_REPLACE 的参数为 1 表示从第一个位置开始，0 表示替换在源字符串中找到的所有匹配项。

SELECT REGEXP_REPLACE('t/h"is"'||chr(9)||'is a|te\st', '([/\|"])', '\\\1', 1, 0) FROM dual;

通过将上述内容包装在 REPLACE 调用中，替换 TAB 和回车很容易，但是必须为每个控制字符执行此操作，这很糟糕。因此，恐怕我的答案并不是真正适合您的完整答案，它只会对您使用常规字符有所帮助:

SQL> SELECT REPLACE(REPLACE(REGEXP_REPLACE('t/h"is"'||chr(9)||'is
  2  a|te\st', '([/\|"])', '\\\1', 1, 0), chr(9), '\t'), chr(10), '\n') fixe
  3  FROM dual;

FIXED
-------------------------
t\/h\"is\"\tis\na\|te\\st

SQL>

编辑:这是一个解决方案! 我并没有声称完全理解它，但基本上它创建了一个连接到您的字符串的转换表(在 inp_str 表中)。 connect by, 级别遍历字符串的长度并替换转换表中匹配的字符。我修改了一个在这里找到的解决方案: http://database.developer-works.com/article/14901746/Replace+%28translate%29+one+char+to+many这真的没有很好的解释。希望这里有人会插话并充分解释这一点。

SQL> with trans_tbl(ch_frm, str_to) as (
     select '"',     '\"' from dual union
     select '/',     '\/' from dual union
     select '\',     '\\' from dual union
     select chr(8),  '\b' from dual union -- BS
     select chr(12), '\f' from dual union -- FF
     select chr(10), '\n' from dual union -- NL
     select chr(13), '\r' from dual union -- CR
     select chr(9),  '\t' from dual       -- HT
   ),
   inp_str as (
     select 'No' || chr(12) || 'w is ' || chr(9) || 'the "time" for /all go\od men to '||
     chr(8)||'com' || chr(10) || 'e to the aid of their ' || chr(13) || 'country' txt from dual
   )
   select max(replace(sys_connect_by_path(ch,'`'),'`')) as txt
   from (
   select lvl
    ,decode(str_to,null,substr(txt, lvl, 1),str_to) as ch
    from inp_str cross join (select level lvl from inp_str connect by level <= length(txt))
    left outer join trans_tbl on (ch_frm = substr(txt, lvl, 1))
    )
    connect by lvl = prior lvl+1
    start with lvl = 1;

TXT
------------------------------------------------------------------------------------------
No\fw is \tthe \"time\" for \/all go\\od men to \bcom\ne to the aid of their \rcountry

SQL>

编辑 8/10/2016 - 使其成为封装和可重用性的函数，以便您可以一次将其用于多列:

create or replace function esc_json(string_in varchar2)
return varchar2
is 
s_converted varchar2(4000);
BEGIN
with trans_tbl(ch_frm, str_to) as (
     select '"',     '\"' from dual union
     select '/',     '\/' from dual union
     select '\',     '\\' from dual union
     select chr(8),  '\b' from dual union -- BS
     select chr(12), '\f' from dual union -- FF
     select chr(10), '\n' from dual union -- NL
     select chr(13), '\r' from dual union -- CR
     select chr(9),  '\t' from dual       -- HT
   ),
   inp_str(txt) as (
     select string_in from dual
   )
   select max(replace(sys_connect_by_path(ch,'`'),'`')) as c_text
   into s_converted   
   from (
   select lvl
    ,decode(str_to,null,substr(txt, lvl, 1),str_to) as ch
    from inp_str cross join (select level lvl from inp_str connect by level <= length(txt))
    left outer join trans_tbl on (ch_frm = substr(txt, lvl, 1))
    )
    connect by lvl = prior lvl+1
    start with lvl = 1;

    return s_converted;
end esc_json;

一次调用多个列的示例:

select esc_json(column_1), esc_json(column_2)
from your_table;