String functions perform the following tasks:
extract substrings (SUBSTRING)
transliterate alphanumeric strings to national strings or vice versa (TRANSLATE)
transcode national strings from UTFE to UTF-16 or vice versa (TRANSLATE)
remove leading or trailing characters of strings (TRIM)
convert uppercase letters to lowercase letters or lowercase letters to uppercase letters (LOWER, UPPER)
convert a value of any data type to the internal presentation (as an alphanumeric string or in hexadecimal format) and vice versa (HEX_OF_VALUE, VALUE_OF_HEX, REP_OF_VALUE, VALUE_OF_REP)
for national strings, supply the collation element in accordance with the Default Unicode Translation Table (COLLATE)
convert national strings to normal form (NORMALIZE)
string_function ::=
{
SUBSTRING ( expression FROM startposition [FOR substring_length ][USING CODE_UNITS])| TRANSLATE ( expression USING [[ catalog .]INFORMATION_SCHEMA.] transname
[DEFAULT character ] [ ,length ]) |
TRIM ([[LEADING |TRAILING | BOTH] [ character ] FROM] expression ) | LOWER ( expression ) | UPPER ( expression ) | HEX_OF_VALUE ( expression2 ) | VALUE_OF_HEX ( expression3 , data_type ) | REP_OF_VALUE ( expression2 ) | VALUE_OF_REP ( expression3 , data_type ) | COLLATE ( expression USING { DUCET_WITH_VARS | DUCET_NO_VARS } [ ,length ]) | NORMALIZE ( expression [,NFC | NFD [, length ]])
}
character ::= expression
length ::= unsigned_integer
expression
Alphanumeric expression or national expression. Its evaluation must return either an alphanumeric string (data type CHAR or VARCHAR) or a national string (data type NCHAR or NVARCHAR). expression may not be a multiple value with dimension > 1. See also section "Compatibility between data types".
Restrictions that apply to a function are described in the description of the relevant function.
expression2
Expression of any data type. The internal presentation of this value is returned as an alphanumeric string or in hexadecimal format.
expression2 may not be a multiple value with dimension > 1.
expression3
Alphanumeric expression which is the internal presentation of a value of the type data_type. This value is the result.
expression3 may not be a multiple value with dimension > 1.
startposition
Integral numeric expression for the position of the start of the substring.
substring_length
Integral numeric expression for the length of the substring.
data_type
Data type of the result.
length
Maximum length of the result string.