为JSON输出转义特殊字符

I have a column that contains data that I want to escape in order to use it as JSON output, to be more precise am trying to escape the same characters listed here but using Oracle 11g: Special Characters and JSON Escaping Rules

我有一个列，其中包含我希望转义的数据，以便将其用作JSON输出，更准确地说，我试图转义这里列出的相同字符，但使用Oracle 11g:特殊字符和JSON转义规则

I think it can be solved using REGEXP_REPLACE:

我认为可以用REGEXP_REPLACE解决:

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

But I am lost about replacing the other characters (tab, new line, backspace, etc), in the previous example I know that \1 will match and replace the first group but I am not sure how to capture the tab in the second group and then replace it with \t. Somebody could give me a hint about how to do the replacement?

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

I know I can do this:

我知道我能做到:

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

But I would have to nest like 5 calls to REGEXP_REPLACE, and I suspect I should be able to do it in just one or two calls.

但是我必须嵌套5次调用REGEXP_REPLACE，我想我应该能够在一两个调用中完成。

I am aware about other packages or libraries for JSON but I think this case is simple enough that it can be solved with the functions that Oracle offers out-of-the-box.

我了解JSON的其他包或库，但我认为这种情况足够简单，可以通过Oracle提供的开箱即用的函数来解决。

Thank you.

谢谢你！

2 个解决方案

#1

Here's a start. Replacing all the regular characters is easy enough, it's the control characters that will be tricky. This method uses a group consisting of a character class that contains the characters you want to add the backslash in front of. Note that characters inside of the class do not need to be escaped. The argument to REGEXP_REPLACE of 1 means start at the first position and the 0 means to replace all occurrences found in the source string.

这是一个开始。替换所有常规字符非常容易，而控制字符则比较棘手。该方法使用一个由字符类组成的组，该字符类包含要在前面添加反斜杠的字符。注意，类内部的字符不需要转义。REGEXP_REPLACE 1的参数表示从第一个位置开始，而0表示替换源字符串中出现的所有情况。

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

Replacing the TAB and a carriage return is easy enough by wrapping the above in REPLACE calls, but it stinks to have to do this for each control character. Thus, I'm afraid my answer isn't really a full answer for you, it only helps you with the regular characters a bit:

通过在替换调用中包装上面的内容，替换选项卡和回车就足够简单了，但是对每个控件字符都必须这样做是很糟糕的。因此，恐怕我的答案对你来说并不是一个完整的答案，它只对你的正常角色有一点帮助:

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>

EDIT: Here's a solution! I don't claim to understand it fully, but basically it creates a translation table that joins to your string (in the inp_str table). The connect by, level traverses the length of the string and replaces characters where there is a match in the translation table. I modified a solution found here: http://database.developer-works.com/article/14901746/Replace+%28translate%29+one+char+to+many that really doesn't have a great explanation. Hopefully someone here will chime in and explain this fully.

编辑:这是一个解决方案!我并不是说完全理解它，但它基本上创建了一个连接到您的字符串的转换表(在inp_str表中)。level的connect by遍历字符串的长度，并替换转换表中匹配的字符。我修改了这里找到的一个解决方案:http://database.developer-works.com/article/14901746/Replace+%28translate%29+ 1 +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>

EDIT 8/10/2016 - Make it a function for encapsulation and reusability so you could use it for multiple columns at once:

编辑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;

Example to call for multiple columns at once:

同时调用多个列的示例:

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

#2

Inspired by the answer above, I created this simpler "one-liner" function:

受以上答案的启发，我创建了这个更简单的“一行”函数:

create or replace function json_esc ( 
    str IN varchar2 
) return varchar2 
begin 
    return REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(str, chr(8), '\b'), chr(9), '\t'), chr(10), '\n'), chr(12), '\f'), chr(13), '\r');
end; 

Please note, both this and @Gary_W's answer above are not escaping all control characters as the json.org seems to indicate.

请注意，上面的这个和@Gary_W的答案都不能像json.org所显示的那样转义所有的控制字符。