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, and byte data types can contain embedded nulls. To write a string literal that matches such values, you can use literals, concatenations of regular quoted string literals, hexadecimal literals, or Unicode literals.
Example of inserting into a BYTE column:
INSERT INTO byte_table VALUES ('This literal ' + x'00' + ' has an embedded null-byte in it')
Hexadecimal Representation
To specify a non-printing character in terminal monitor, you can use a hex (hexadecimal) constant. The hex constants actually represent a varbyte (see Byte Literals), but can be converted to strings.
For example, the following is converted to the ASCII string ABC<carriage return>:
X'4142430D'
A = X'41', B = X'42', C = X'43', and carriage return = X'OD'.
(The terminal monitor implicitly converts the bytes to a string.)
Be careful when using hex constants for strings because the hex values depend on the used character set. For example, the euro sign € has a hex value of x'A4' when using ISO8859-15, but x'80' in WINDOWS-1252. Moreover, in UTF8 the euro sign takes three bytes: x'E2', x'82', x'AC'. You would produce invalid UTF8 data when using one of the other representations above. Instead, it is better to use Unicode literals for the euro sign: u&'\20AC'.
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.
Last modified date: 04/03/2024