ODBC and JDBC Guide

Chapter 7 | Supported standards 51
Functions
FileMaker SQL supports many functions you can use in expressions. Some of the functions return
characters strings, some return numbers, and some return dates.
Functions that return character strings
Note The TIME() function is deprecated. Use the SQL standard CURRENT_TIME instead.
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