转义特殊字符以进行JSON输出

Question

转义特殊字符以进行JSON输出

6

我有一列包含数据，我想对其进行转义以便将其用作JSON输出，更准确地说，我正在尝试使用Oracle 11g转义此处列出的相同字符：特殊字符和JSON转义规则我认为可以使用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提供的开箱即用的函数来解决。

谢谢。

- Abel Morelos

3个回答

2

受上面答案的启发，我创建了这个更简单的“一行代码”函数：

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;

请注意，无论是本答案还是上面的@Gary_W答案，都没有转义json.org似乎指示的所有控制字符。

- billc.cn

2

哦，你没有转义最重要的“”和\！ - MGM

0

在SQL Server中，您可以使用以下方式使用STRING_ESCAPE()函数：

SELECT 
    STRING_ESCAPE('[''  This is a special / "message" /'']', 'json') AS
    escapedJson;

- Mahdi Hesari

1

这对我来说是最好的答案。谢谢！ - kanagaraj palanisamy

这个仅存在于2016版本及以后。之前的版本需要像其他答案中的自定义函数一样。https://learn.microsoft.com/en-us/sql/t-sql/functions/string-escape-transact-sql?view=sql-server-ver15 - Tony

这是针对SQL Server而非Oracle的。 - Jeffrey Kemp

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gary_W · Accepted Answer

以下是一些起点。替换所有常规字符相当简单，但控制字符可能有些棘手。该方法使用一个由包含要在其前面添加反斜杠的字符的字符类组成的组。请注意，类内部的字符不需要转义。REGEXP_REPLACE的参数1表示从第一个位置开始，0表示替换源字符串中找到的所有出现。

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

替换制表符和换行符很容易，只需将上述内容包裹在REPLACE调用中即可。但是，每个控制字符都要这样做，这种做法不太好。因此，我担心我的答案并不能完全解决您的问题，它只能帮助您处理一些常规字符:

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，level遍历字符串的长度，并在翻译表中有匹配项的位置替换字符。我修改了在这里找到的解决方案，但该方案并没有很好地解释。希望这里会有人加入并完全解释一下。

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>

编辑 2016年8月10日 - 把它变成一个函数，以实现封装和可重用性，这样你可以一次性使用它来处理多个列：

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;