ALLBASE/SQL Reference Manual (36216-90216)

Chapter 8 253
Expressions
String Functions
String Functions
String functions return partial values or attributes of character and BINARY (including
LONG) string data.
With the G3 release of ALLBASE/SQL and IMAGE/SQL, the supported SQL syntax has
been enhanced to include the following string manipulation functions: UPPER, LOWER,
POSITION, INSTR, TRIM, LTRIM and RTRIM. These string functions allow you to
manipulate or examine the CHAR and VARCHAR values within the SQL syntax, allowing for
more sophisticated queries and data manipulation commands to be formed. These string
functions were designed to be compatible with functions specified in the ANSI SQL '92
standard and functions used in ORACLE. In cases where the ANSI SQL '92 standard and
the ORACLE functions were not compatible (such as the LTRIM and RTRIM in ORACLE
versus TRIM in the ANSI standard), both versions were implemented. The specifications for
each of these functions follows.
Function Specification
LOWER
Converts all the characters in
stringexpr to lower case
Syntax
[LOWER ( stringexpr )]
UPPER
Converts all the characters in
stringexpr to upper case
Syntax
[UPPER ( stringexpr )]
POSITION
Searches for the presence of the string
stringexpr1 in the string stringexpr2 and
returns a numeric value that indicates the position at which
stringexpr1 is found in
stringexpr2
Syntax [POSITION ( stringexpr,stringexpr2 )]
INSTR
Searches
stringexpr1 beginning with its n th character for the m th occurrence of
stringexpr2 and returns the position of the character in stringexpr1 that is the first
character of this occurrence. If
n is negative, Instr counts and searches backward from the
end of
stringexpr1 . The value of m must be positive. The default values of both n and m are
1, meaning Instr begins searching at the first character of
stringexpr1 for the first
occurrence of
stringexpr2 . The return value is relative to the beginning of stringexpr1
regardless of the value of n , and is expressed in characters. If the search is unsuccessful (if
stringexpr2 does not appear m times after the n th character of stringexpr1 ) the return
value is 0.
If
n and m are not specified the function is equivalent to the ANSI SQL-92 POSITION