JSON_QUOTE

Description

Surrounds the input string parameter with double quotes and escapes special characters and control characters in the string. The main purpose of this function is to convert strings into valid JSON strings.

Special characters include:

• Quote (")
• Backslash (\)
• Backspace (\b)
• Newline (\n)
• Carriage return (\r)
• Horizontal tab (\t)

Control characters include:

• CHAR(0) is escaped as \u0000

Syntax

JSON_QUOTE (<str>)

Parameters

<str> String type, the value to be quoted.

Return Value

Returns a string enclosed in double quotes

Usage Notes

• If the parameter is NULL, returns NULL.
• If the parameter contains escape symbol (\) + non-escape character, the escape symbol will be removed, see examples 4 and 5.

Examples

1. Double quotes are escaped

   select json_quote('I am a "string" that contains double quotes.');

   +------------------------------------------------------------+
   | json_quote('I am a "string" that contains double quotes.') |
   +------------------------------------------------------------+
   | "I am a \"string\" that contains double quotes."           |
   +------------------------------------------------------------+

2. Escaping special characters

   select json_quote("\\ \b \n \r \t");

   +------------------------------+
   | json_quote("\\ \b \n \r \t") |
   +------------------------------+
   | "\\ \b \n \r \t"             |
   +------------------------------+

3. Control character escaping

   select json_quote("\0");

   +------------------+
   | json_quote("\0") |
   +------------------+
   | "\u0000"         |
   +------------------+

4. Escape symbol + non-escape character case

   select json_quote("\a");

   +------------------+
   | json_quote("\a") |
   +------------------+
   | "a"              |
   +------------------+

5. Non-zero unprintable characters

   select json_quote("\1");

   +------------------+
   | json_quote("\1") |
   +------------------+
   | "1"              |
   +------------------+