SQL Reference

Chapter 2 | Supported standards 30
You cannot use an aggregate function as an argument to other functions. If you do, FileMaker
returns the error code 8309 (“Expressions involving aggregations are not supported”). For
example, the following statement is not valid because the aggregate function SUM cannot be used
as an argument to the function ROUND:
SELECT ROUND(SUM(Salary), 0) FROM Payroll
However, aggregate functions can use functions that return numbers
as arguments. For example,
the following statement is valid:
SELECT SUM(ROUND(Salary, 0)) FROM Payroll
Functions that return character strings
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 co
nnect time
DAYNAME Returns the name of the day that corresponds to
a specifie
d date
RTRIM Removes trailing blanks from a string RTRIM(' ABC ') returns ' ABC'
TRIM Removes leading and trail
ing 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 strin
g to uppercase UPPER('Allen') returns 'ALLEN'
LOWER Changes each letter of a strin
g to lowercase LOWER('Allen') returns 'allen'
LEFT Returns leftmost characters of a string LEFT('Mattson',3) r
eturns '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) retu
rns ' '
STRVAL Converts a value of any type to a character
string
STRVAL('Woltman') r
eturns 'Woltman'
STRVAL(5 * 3) returns '15'
STRVAL(4 = 5) returns 'Fal
se'
STRVAL(DATE '2008-12-25') 
returns '20
08-12-25'
TIME
TIMEVAL
Returns the time of day as a string At 9:49 PM, TIME() return
s 21:49:00
USERNAME
USER
Returns the login ID specified at co
nnect time
Note The TIME() function is deprecated. Use the SQL standard CURRENT_TIME instead.