ODBC and JDBC Guide

Chapter 7
|
Supported standards 51
Functions that return character strings
Note The TIME() function is deprecated. Use the SQL standard CURRENT_TIME instead.
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
Functions that
return character
strings Description Example
CHR Converts an ASCII code to a one-character string CHR(67) returns C
CURRENT_USER Returns the login ID specified at connect time
DAYNAME Returns the name of the day that corresponds to a
specified date.
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'
MONTHNAME Returns the names of the calendar month.
RIGHT Returns rightmost characters of a string RIGHT('Mattson',4) returns 'tson'
SUBSTR
SUBSTRING
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({D '2008-12-25'})
returns '2008-12-25'
TIME
TIMEVAL
Returns the time of day as a string At 9:49 PM, TIME() returns 21:49:00
USERNAME
USER
Returns the login ID specified at connect time