Chapter 5
|
Supported standards 41
Functions
The ODBC and JDBC client drivers support many functions you can use in expressions. Some of the
functions return characters strings, some return numbers, and some return dates.
Important Use uppercase letters for SQL function names (some are case sensitive).
Examples
SELECT CHR(67) + SPACE(1) + CHR(70) FROM Salespeople
SELECT RTRIM(' ' + Salespeople.Salesperson_ID) AS agg FROM Salespeople
SELECT TRIM(SPACE(1) + Salespeople.Salesperson_ID) AS agg FROM Salespeople
SELECT LTRIM(' ' + Salespeople.Salesperson_ID) AS agg FROM Salespeople
SELECT UPPER(Salespeople.Salesperson) AS agg FROM Salespeople
SELECT LOWER(Salespeople.Salesperson) AS agg FROM Salespeople
SELECT LEFT(Salespeople.Salesperson, 5) AS agg FROM Salespeople
SELECT RIGHT(Salespeople.Salesperson, 7) AS agg FROM Salespeople
SELECT SUBSTR(Salespeople.Salesperson_ID, 2, 2) +
SUBSTR(Salespeople.Salesperson_ID, 4, 2) AS agg FROM Salespeople
SELECT SUBSTR(Salespeople.Salesperson_ID, 2) +
SUBSTR(Salespeople.Salesperson_ID, 4) AS agg FROM Salespeople
Functions that
return character
strings
Description Example
CHR Converts an ASCII code to a one-character
string
CHR(67) returns C
RTRIM Removes trailing blanks from a string RTRIM('ABC ') returns ABC
TRIM Removes leading and trailing blanks from a
string
TRIM(' ABC ') returns ABC
LTRIM Removes leading blanks from a string LTRIM(' ABC') returns ABC
UPPER Changes each letter of a string to uppercase UPPER('Allen') returns ALLEN
LOWER Changes each letter of a string to lowercase LOWER('Allen') returns allen
LEFT Returns leftmost characters of a string LEFT('Mattson',3) returns Mat
RIGHT Returns rightmost characters of a string RIGHT('Mattson',4) returns tson
SUBSTR Returns a substring of a string, with parameters
of the string, the first character to extract, and the
number of characters to extract (optional)
SUBSTR('Conrad',2,3) returns onr
SUBSTR('Conrad',2) returns onrad
SPACE Generates a string of blanks SPACE(5) returns ' '
STRVAL Converts a value of any type to a character
string
STRVAL('Woltman') returns Woltman
STRVAL(5 * 3) returns 15
STRVAL(4 = 5) returns 'False'
STRVAL({12/25/2004}) returns 12/25/2004
TIME Returns the time of day as a string At 9:49 PM, TIME() returns 21:49:00
USERNAME Returns the login ID specified at connect time
