SQL elements that support internationalization and localization

The CHARACTER data type represents a null-terminated character field of length length.

length represents the number of characters.

The VARCHAR data type represents a variable-length character field whose maximum length is length.

length represents the maximum number of characters.

Relational operators specify how SQL compares expressions in basic and quantified predicates.

To sort and compare CHARACTER data, the SQL server uses the numeric values in the collation table, not the numeric values in the code page.

The LIKE predicate searches for strings that have a certain pattern. The pattern is specified after the LIKE keyword in a string constant. The pattern can be specified by a string in which the underscore (_) and percent sign (%) characters have special semantics.

The LIKE predicate is multi-byte enabled. The string_constant and the escape_character can contain multi-byte characters, and the escape_character can be a multi-byte character. A percent sign (%) or an underscore (_) in the string_constant can represent a multi-byte character. However, the percent sign or underscore itself must be the single-byte ASCII encoding. The comparison is case-insensitive.

'char-string'

A character-string literal is a string of characters enclosed in single quotation marks (' '). To include a single quotation mark in a character-string literal, precede it with an additional single quotation mark.

A character string literal can contain multi-byte characters in the character set used by the SQL client. Only single-byte ASCII-encoded quote marks are valid in the syntax.

 

SQL supports special formats for date and time literals. Basic predicates and the VALUES clause of INSERT statements can specify date literals directly for comparison and insertion into tables. In other cases, you need to convert date literals to the appropriate date-time data type with the CAST, CONVERT, or TO_DATE scalar functions.

All text (names of days, months, ordinal number endings) in all types date-format literals must be in the English language. The default date format is American. You can explicitly request another date format by using a format string. Time literals are only in the English language.

The scalar function ASCII returns the ASCII value of the first character of the given character expression.

The ASCII function depends on the code page and supports multi-byte characters. The function returns the character encoding integer value of the first character of char_expression in the current code page. Whether char_expression represents a literal string or a database field, the result depends on the code page of the database.

The scalar function CHAR returns a character string with the first character having an ASCII value equal to the argument expression.CHAR is identical to CHR but provides ODBC-compatible syntax.

The CHAR function depends on the code page and supports single-byte and multi-byte characters. If integer_expression evaluates to an integer value that represents a character in the database code page, CHAR returns that character. Otherwise, CHAR returns a NULL value.

The scalar function CHR returns a character string with the first character having an ASCII value equal to the argument expression.

The CHR function depends on the code page and supports single-byte and multi-byte characters. If integer_expression evaluates to an integer value that represents a character in the database code page, CHR returns that character. Otherwise, CHR returns a NULL value.

The scalar function CONCAT returns a concatenated character string formed by concatenating argument one with argument two.

The two char_expression expressions and the result of the CONCAT function can contain multi-byte characters.

The ABL Extension scalar function CONVERT converts an expression to another data type. The first argument is the target data type. The second argument is the expression to be converted to that type.

When data_type is CHARACTER(length ) or VARCHAR(length ), the length specification represents the number of characters. The converted result can contain multi-byte characters.

The scalar function GREATEST returns the greatest value among the values of the given expressions.

When the data type of an expression is either CHARACTER(length) or VARCHAR(length), the expression can contain multi-byte characters. The sort weight for each character is determined by the collation table in the database.

The scalar function INITCAP returns the result of the argument character expression after converting the first character to uppercase and the subsequent characters to lowercase.

A char_expression and the result can contain multi-byte characters. To convert the first character to uppercase and the subsequent characters to lowercase, OpenEdge uses a case table in the convmap.cp file. The default case table is BASIC.

The scalar function INSERT returns a character string where length number of characters have been deleted from string_exp1 beginning at start_pos, and string_exp2 has been inserted into string_exp1, beginning at start_pos.

string_exp1, string_exp2, and the result might contain multi-byte characters, depending on the code page of the SQL server. The length argument specifies a character count.

The INSTR (in string) scalar function searches character string char_expression1 for the character string char_expression2.The search begins at start_pos of char_expression1. If occurrence is specified, then INSTR searches for the nth occurrence, where n is the value of the fourth argument.

A char_expression and the result can contain multi-byte characters.

The scalar function LCASE returns the result of the argument character expression after converting all the characters to lowercase.LCASE is the same as LOWER but provides ODBC-compatible syntax.

A char_expression and the result can contain multi-byte characters. The conversion to lowercase conversion depends on the case table in the convmap file. The default case table is BASIC.

The scalar function LEAST returns the lowest value among the values of the given expressions.

When the data type of an expression is either CHARACTER(length) or VARCHAR(length), the expression can contain multi-byte characters. The sort weight for each character depends on the collation table in the database.

The scalar function LEFT returns the leftmost count of characters of string_exp.

The string_exp and the result can contain multi-byte characters. The function returns a character count.

The scalar function LENGTH returns the string length of the value of the given character expression.

char_expression can contain multi-byte characters. The function returns a character count.

 

The scalar function LOCATE returns the location of the first occurrence of char_expr1 in char_expr2. If the function includes the optional integer argument start_pos, LOCATE begins searching char_expr2 at that position. If the function omits the start_pos argument, LOCATE begins its search at the beginning of char_expr2.

char_expr1 and char_expr2 can contain multi-byte characters. The start_pos argument specifies a character position, not a byte position. Character comparisons use the collation table in the database.

 

The scalar function LPAD pads the character string corresponding to the first argument on the left with the character string corresponding to the third argument. After the padding, the length of the result is length.

The char_expression and pad_expression can contain multi-byte characters. The length specifies a number of characters.

The scalar function LTRIM removes all the leading characters in char_expression that are present in char_set and returns the resulting string. The first character in the result is guaranteed not to be in char_set. If you do not specify the char_set argument, leading blanks are removed.

The char_expression, the character set specified by char_set, and the result can contain multi-byte characters. Character comparisons are case-sensitive and depend on the collation table in the database.

The scalar function PREFIX returns the substring of a character string, starting from the position specified by start_pos and ending before the specified character.

Each char_expression and the result can contain multi-byte characters. The start_pos argument specifies a character position, not a byte position. Character comparisons are case-sensitive and depend on sort weights in the collation table in the database.

The scalar function REPEAT returns a character string composed of string_exp repeated count times.

string_exp and the result can contain multi-byte characters.

The scalar function REPLACE replaces all occurrences of string_exp2 in string_exp1 with string_exp3.

Each occurrence of string_exp and the result can contain multi-byte characters. Character comparisons are case-sensitive and depend on sort weights in the collation table in the database.

The scalar function RIGHT returns the rightmost count of characters of string_exp.

Each occurrence of string_exp and the result can contain multi-byte characters. Character comparisons are case-sensitive and depend on sort weights in the collation table in the database.

The scalar function RPAD pads the character string corresponding to the first argument on the right with the character string corresponding to the third argument. After the padding, the length of the result is equal to the value of the second argument length.

char_expression and pad_expression can contain multi-byte characters. length represents the number of characters in the result.

The scalar function RTRIM removes all the trailing characters in char_expression that are present in char_set and returns the resultant string. The last character in the result is guaranteed not to be in char_set. If you do not specify a char_set, trailing blanks are removed.

The char_expression, the character set specified by char_set, and the result can contain multi-byte characters. Character comparisons are case-sensitive and depend on the collation table in the database.

The scalar function SUBSTR returns the substring of the character string corresponding to the first argument starting at start_pos and length characters long. If the third argument length is not specified, the substring starting at start_pos up to the end of char_expression is returned.

char_expression and the result can contain multi-byte characters. length specifies a number of characters. Character comparisons are case-sensitive and depend on sort weights in the collation table in the database.

The scalar function SUFFIX returns the substring of a character string starting after the position specified by start_pos and the second char_expression, to the end of the string.

Each char_expression and the result can contain multi-byte characters. The start_pos argument specifies a character position, not a byte position. Character comparisons are case-sensitive and depend on sort weights in the collation table in the database.

The scalar function UCASE returns the result of the argument character expression after converting all the characters to uppercase. UCASE is identical to UPPER, but provides ODBC-compatible syntax.

A char_expression and the result can contain multi-byte characters. The conversion to uppercase depends on the case table in the convmap file. The default case table is BASIC.

The scalar function UPPER returns the result of the argument character expression after converting all the characters to uppercase.

A char_expression and the result can contain multi-byte characters. The conversion to uppercase depends on the case table in the convmap file. The default case table is BASIC.