String Literals
String literals are specified by one or more characters enclosed in single quotes. The default data type for string literals is VARCHAR, but a string literal can be assigned to any character data type or to money or date data type without using a data type conversion function.
To compare a string literal with a non-character data type, you must either cast the string literal to the non-character data type or cast the non-character data type to the string literal type. Failure to do so causes unexpected results if the non-character data type contains the ‘NULL (0) value.
Hexadecimal Representation
To specify a non-printing character in terminal monitor, use a hex (hexadecimal) constant.
Hex constants are specified by an X followed by a single-quoted string composed of (an even number of) alphanumeric characters. For example, the following represents the ASCII string ABC<carriage return>:
X'4142430D'
A = X'41', B = X'42', C = X'43', and carriage return = X'OD'.
An alternative is to use a prefix of 0x. That is, 0xstring composed of (an even number of) alphanumeric characters:
0x4142430D
A = 0x41, B = 0x42', C = 0x43, and carriage return = 0xOD.
Note: Tables of structure VECTORWISE and VECTORWISE_ROW do not support the BYTE data type. A string literal can be used to hold BYTE values but be careful when using functions that may result in embedded NULL (\000) being placed in a string because all values after the first NULL will be permanently lost. The values 'A\000BCD' will be stored as 'A'.
Quotes within Strings
To include a single quote inside a string literal, it must be doubled. For example:
'The following letter is quoted: ''A''.'
which is evaluated as:
The following letter is quoted: 'A'.
Unicode Literals
A Unicode string literal in SQL is identified by prefixing the string either with U& or with the letter N. The characters present in the string will be converted to Unicode code points and the data type of the resulting literal will be NVARCHAR.
Unicode code points can also be embedded in the literal using their numeric form. Being able to encode code points numerically allows the literal to contain characters that may have no representation in the character set of the SQL client interface, such as the terminal monitor. Characters from the Basic Multilingual Plane can be embedded using the escape character \ followed by the four hexadecimal digits of its value. The code points from the remaining Supplementary Planes all use six hexadecimal digits and are escaped with +. For example:
U&’Hello\202Fworld+029E71’
In this string, Hello is converted to the equivalent Unicode code points, the hex digits 202F represent code point U+202F (the narrow non-breaking space), world is converted to the equivalent Unicode code points, and the hex digits 029E71 represent code point U+29E71 (the Chinese ideograph chù from Plane 2). The resulting literal is the concatenation of the converted Unicode components.