Was this helpful?
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.
SQL will attempt to implicitly cast a string literal to a non-string type such as integer, if required. To avoid unexpected results, however, it is better to use the appropriate non-string literal, or explicitly cast the string literal to the desired type.
For example, given a cust table with a cust_no integer column:
SELECT * FROM cust WHERE cust_no = '12345'
will work but requires SQL to guess at the proper coercion. Preferred syntax is:
SELECT * FROM cust WHERE cust_no = int4('12345')
or simply
SELECT * FROM cust WHERE cust_no = 12345
Strings can contain embedded non-printing characters. To write a string literal that matches such values, you can use literals or concatenations of regular quoted string literals, hexadecimal literals, or Unicode literals.
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 X100 and X100_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
To specify a Unicode literal value within a non-Unicode command string (for example, in a query entered into the terminal monitor), the Unicode literal notation can be used. A Unicode literal is a sequence of ASCII characters intermixed with escaped sequence of hex digits, all enclosed in quotes and preceded by U&. The escape character \ precedes sets of 4 hex digits that are treated as Unicode codepoints from Plane 0 (the Basic Multilingual Plane) and the escape sequence \+ precedes sequences of 6 hex digits that are treated as codepoints from the Supplementary Plane. Note that this may require a leading 0. For example:
U&’Hello\202Fworld\+029E71’
In this string, Hello is converted to the equivalent Unicode codepoints, the hex digits 202F represent codepoint U+202F (the narrow non-breaking space), world is converted to the equivalent Unicode codepoints, and the hex digits 029E71 represent codepoint U+29E71 (the Chinese ideograph chù from Plane 2). The resulting literal is the concatenation of the converted Unicode components.
The notation N'x' can be used as an alternative to the U&'x' notation.
Last modified date: 11/09/2022