Escape Character

Last Updated: Jun 10, 2016

In ODPS SQL, a string constant can be quoted by single or double quotation marks. The string quoted by single quotation marks can contain double quotation marks or the string quoted by double quotation marks can contain single quotaton marks. Otherwise, you must use escape character to indicate it. The following expressions are acceptable:

  1. "I'm a happy manong!"
  2. 'I\'m a happy manong!'

In ODPS SQL, ‘\’ is a kind of escape character, used to express the special character in string or express its followed chracters as characters themselves. To read a string constant, if ‘\’ is followed by three effective 8 hexadecimal digits and corresponding range is from 001 to 177, system will convert it to corresponding characters according to ASCII value. The following table lists some special characters:

Escape Character Character
\b backspace
\t tab
\n newline
\r carriage-return
\’ Single quotation mark
\” Double quotation marks
\ \ back-slant
\; semicolon
\Z control-Z
\0 or \00 tailed
  1. select length('a\tb') from dual;

The result is 3, which indicates that there are three characters in the string. ‘\t’ is considered as one character. Other characters followed behind it are expressed as themselves.

  1. select 'a\ab',length('a\ab') from dual;

The result: ‘aab’, 3. ‘\a’ is expressed as general ‘a’.

Thank you! We've received your feedback.