ODBC and JDBC Guide

Chapter 7 | Supported standards 52
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 = 1
Functions that return numbers
Functions that
return numbers Description Example
ABS Returns the absolute value of the numeric expression
ATAN Returns the arc tangent of the argument as an angle
expressed in radians
ATAN2 Returns the arc tangent of x and y coordinates as an
angle expressed in radians
B Returns the decimal equivalent of a binary number B'1001' returns 9
CEIL
CEILING
Returns the smallest integer value that is greater than
or equal to the argument
DEG
DEGREES
Returns the number of degrees of the argument,
which is an angle expressed in radians
DAY Returns the day part of a date DAY({d '2012/01/30'}) returns 30
DAYOFWEEK Returns the day of week (1-7) of a date expression DAYOFWEEK({d '2004/05/01'})
returns 7
MOD Divides two numbers and returns the remainder of the
division
MOD(10,3) returns 1
EXP Returns a value that is the base of the natural
logarithm (e) raised to a power specified by the
argument
FLOOR Returns the largest integer value that is less than or
equal to the argument
HOUR Returns the hour part of a value.
INT Returns the integer part of a number INT(6.4321) returns 6
LEN
LENGTH
Returns the length of a string LEN('ABC') returns 3
MONTH Returns the month part of a date MONTH({d '2012/01/30'}) returns 1