ODBC and JDBC Developer’s Guide

38 FileMaker ODBC and JDBC Developer’s Guide
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
SELECT SPACE(2) + Salespeople.Salesperson_ID AS Salesperson_ID FROM Salespeople
SELECT STRVAL('60506') AS agg FROM Sales_Data WHERE Sales_Data.Invoice_ID = 1
SELECT TIME() AS agg FROM Sales_Data WHERE Sales_Data.Invoice_ID = 1
SELECT USERNAME() AS agg FROM Sales_Data WHERE Sales_Data.Invoice_ID = 1
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
Functions that
return numbers Description Example
MOD Divides two numbers and returns the
remainder of the division
MOD(10,3) returns 1
LEN Returns the length of a string LEN('ABC') returns 3
MONTH Returns the month part of a date MONTH({01/30/2004}) returns 1
DAY Returns the day part of a date DAY({01/30/2004}) returns 30
YEAR Returns the year part of a date YEAR({01/30/2004}) returns 2004
MAX Returns the larger of two numbers MAX(66,89) returns 89
Functions that
return character
strings Description Example