Release 10.1A: OpenEdge Data Management:
SQL Reference

> ## String functions

> Table 65 lists the string functions that ODBC supports.

> The string functions listed can take the following arguments:

> - string_exp can be the name of a column, a string literal, or the result of another scalar function, where the underlying data type is `SQL_CHAR` or `SQL_VARCHAR`.

> - start, length, and count can be the result of another scalar function or a literal numeric value, where the underlying data type is `SQL_TINYINT`, `SQL_SMALLINT`, or `SQL_INTEGER`.

> The string functions are one-based; that is, the first character in the string is character 1.

> Character string literals must be surrounded in single quotation marks.

> Table 65: Scalar string functions
> String function | Returns
> --- | ---
>     ASCII(string_exp)
> | ASCII code value of the leftmost character of string_exp as an integer.
>     BIT_LENGTH(string_exp)
>     ODBC 3.0
> | The length in bits of the string expression.
>     CHAR(code)
> | The character with the ASCII code value specified by code. code should be between 0 and 255; otherwise, the return value is data-source dependent.
>     CHAR_LENGTH(string_exp)
>     ODBC 3.0
> | The length in characters of the string expression, if the string expression is of a character data type; otherwise, the length in bytes of the string expression (the smallest integer not less than the number of bits divided by 8). (This function is the same as the `CHARACTER_LENGTH` function.)
>     CHARACTER_LENGTH(string_exp)
>     ODBC 3.0
> | The length in characters of the string expression, if the string expression is of a character data type; otherwise, the length in bytes of the string expression (the smallest integer not less than the number of bits divided by 8). (This function is the same as the `CHAR_LENGTH` function.)
>     CONCAT(string_exp1,string_exp)
> | The string resulting from concatenating string_exp2 and string_exp1. The string is system dependent.
>     DIFFERENCE(string_exp2 and
>     string_exp1)
> | An integer value that indicates the difference between the values returned by the `SOUNDEX` function for string_exp2 and string_exp1.
>     INSERT(string_exp1 , start,
>     length, string_exp2)
> | A string where length characters have been deleted from string_exp1 beginning at start and where string_exp2 has been inserted into string_exp, beginning at start.
>     LCASE(string_exp)
> | Uppercase characters in string_exp converted to lowercase.
>     LEFT(string_exp, count)
> | The count of characters of string_exp.
>     LENGTH(string_exp)
> | The number of characters in string_exp, excluding trailing blanks and the string termination character.
>     LOCATE(string_exp1, string_exp2
>     [,start,])
> | The starting position of the first occurrence of string_exp1 within string_exp2. If start is not specified the search begins with the first character position in string_exp2. If `start` is specified, the search begins with the character position indicated by the value of start. The first character position in string_exp2 is indicated by the value 1. If string_exp1 is not found, 0 is returned.
>     LTRIM(string_exp)
> | The characters of string_exp, with leading blanks removed.
>     OCTET_LENGTH(string_exp)
>     ODBC 3.0
> | The length in bytes of the string expression. The result is the smallest integer not less than the number of bits divided by 8.
>     POSITION(character_exp IN
>     character_exp)
> | The position of the first character expression in the second character expression. The result is an exact numeric with an implementation-defined precision and a scale of 0.
>     REPEAT(string_exp, count)
> | A string composed of string_exp repeated count times.
>     REPLACE(string_exp1, string_exp2,
>     string_exp3)
> | Replaces all occurrences of string_exp2 in string_exp1 with string_exp3.
>     RIGHT(string_exp, count)
> | The rightmost count of characters in string_exp.
>     RTRIM(string_exp)
> | The characters of string_exp with trailing blanks removed.
>     SPACE(count)
> | A string consisting of count spaces.
>     SUBSTRING(string_exp , start,
>     length)
> | A string derived from string_exp beginning at the character position start for length characters.
>     UCASE(string_exp)
> | Lowercase characters in string_exp converted to uppercase.

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095