Use escape characters to represent special characters in SQL JSON and regular expressions - MaxCompute

MaxCompute uses a backslash (\) as the escape character in SQL string literals, regular expressions, and JSON. When working across these contexts, you often need to apply multiple layers of escaping — which can make strings hard to read. This topic covers the escape sequences for each context and shows how to handle nested escaping correctly.

Not all specifications use \ as the escape character. For example, URLs use a percent sign %, and XML uses an ampersand &.

If your string contains many backslashes, use the R"()" raw string literal syntax to skip escaping entirely. See Raw string literals.

SQL escape sequences

In MaxCompute SQL, string literals are enclosed in single or double quotation marks — 'abc' or "abc". If the string contains one type of quotation mark, enclose it in the other:

"I'm a happy developer."      -- single quote inside double-quoted string
'She said "hello".'           -- double quote inside single-quoted string

When the string contains both types of quotation marks, or contains invisible characters such as newlines, use escape sequences. The following table lists all supported escape sequences.

Escape sequence	Description
`\b`	Backspace
`\t`	Tab
`\n`	Line feed (new line)
`\r`	Carriage return
`\'`	Single quotation mark
`\"`	Double quotation mark
`\\`	Backslash
`\;`	Semicolon
`\Z`	Ctrl-Z
`\0` or `\00`	Null terminator
`\<other>`	The character itself — the backslash is dropped. For example, `\a` is equivalent to `a`.

Example: Represent a string that contains both types of quotation marks and a line feed:

-- Using single-quoted string:
'Double quotation mark(")\nSingle quotation mark(\')'

-- Using double-quoted string:
"Double quotation mark(\")\nSingle quotation mark(')"

LIKE pattern matching

When using LIKE to match a literal % or _, escape the character with \:

SELECT 'ab_cde' LIKE 'ab\_c%';

-- Result:
true

Regular expression escape sequences

MaxCompute uses the RE2 regex engine. In a regular expression pattern, \ introduces character class shortcuts for common categories:

Escape sequence	Description
`\d`	Digit — equivalent to `[0-9]`
`\D`	Non-digit — equivalent to `[^0-9]`
`\s`	Whitespace character — equivalent to `[\t\n\f\r ]`
`\S`	Non-whitespace character — equivalent to `[^\t\n\f\r ]`
`\w`	Alphanumeric character — equivalent to `[0-9A-Za-z_]`
`\W`	Non-alphanumeric character — equivalent to `[^0-9A-Za-z_]`

Use regexr or regex101 to build and test regular expressions interactively.

JSON escape sequences

JSON uses a backslash as the escape character. The following escape sequences are defined by the JSON specification:

Escape sequence	Description
`\"`	Double quotation mark
`\\`	Backslash
`\/`	Forward slash
`\b`	Backspace
`\f`	Form feed
`\n`	Line feed
`\r`	Carriage return
`\t`	Horizontal tab
`\u` + 4-digit hex	Unicode character

Use JSONLint to validate JSON strings.

Examples

Special characters in SQL strings

\t counts as one character:

SELECT LENGTH('a\tb');

-- Result:
3

\a is not a recognized escape sequence, so the backslash is dropped and \a becomes a:

SELECT 'a\ab', LENGTH('a\ab');

-- Result:
aab, 3

Regex + SQL escaping

To extract 010 from 010-12345678 using a regular expression:

Write the regex: (\d+)-
Escape the backslash for SQL: (\\d+)-
Enclose in single quotation marks: '(\\d+)-'

SELECT REGEXP_EXTRACT('010-12345678', '(\\d+)-');

-- Result:
010

JSON + SQL escaping

The JSON string {"key":"this is very \"important\"."} contains SQL escape sequences (\"). When passed directly to get_json_object, the SQL parser consumes the backslashes first, turning \" into ". The resulting string is no longer valid JSON, so the function returns NULL:

SET odps.sql.udf.getjsonobj.new=true;
SELECT get_json_object('{"key":"this is very \"important\"."}', '$.key');

-- Result:
NULL

Verify what the SQL parser actually sees:

SELECT '{"key":"this is very \"important\"."}';

-- Result:
{"key":"this is very "important"."}

The backslashes are gone. To pass a valid JSON string through SQL, double-escape the backslashes (\" → \\"):

SET odps.sql.udf.getjsonobj.new=true;
SELECT get_json_object('{"key":"this is very \\"important\\"."}', '$.key');

-- Result:
this is very "important".

JSON + regex + SQL escaping

To match important from the JSON string {"key":"this is very \"important\"."} using a regular expression:

Write the regex to match the quoted word: \"(.*)\"
Escape backslashes for the regex engine: \\"(.*)\\"
Escape backslashes again for SQL: '\\\\"(.*)\\\\"'

SELECT REGEXP_EXTRACT('{"key":"this is very \\"important\\"."}', '\\\\"(.*)\\\\"');

-- Result:
important

When the nesting is this deep, parse the JSON first, then apply the regex — the expression stays readable:

SET odps.sql.udf.getjsonobj.new=true;
SELECT REGEXP_EXTRACT(
  get_json_object('{"key":"this is very \\"important\\"."}', '$.key'),
  '"(.*)"'
);

-- Result:
important

To reverse-engineer what '\\\\"(.*)\\\\"' actually matches, run it through SELECT:

SELECT '\\\\"(.*)\\\\"';

-- Result:
\\"(.*)\\"

Raw string literals

When SQL, JSON, and regular expressions are combined, backslashes multiply quickly. The R"()" syntax lets you write the literal string exactly as it appears — no backslash escaping needed inside the parentheses.

The following table shows how raw string literals reduce backslash noise:

Regular string	Raw string literal
`'abc'`	`R"(abc)"`
`'\\\\"(.*)\\\\"'`	`R'(\\"(.*)\\")'`

Syntax notes:

R is case-insensitive — r"()" works equally well.
Single or double quotation marks are both valid: R"()" or R'()'.
Only the delimiters change; the content inside the parentheses is taken literally.

The JSON + regex example from the previous section becomes much cleaner with raw string literals:

SELECT REGEXP_EXTRACT(R'({"key":"this is very \"important\"."})', R'(\\"(.*)\\")');

-- Result:
important