The c-tree Plus ODBC Driver supports ODBC scalar functions which may be included in an SQL statement as a primary expression. For a description of the SQL syntax for these functions, see the ODBC Programmer's Reference.
String Functions
CONCAT
LCASE
LEFT
LENGTH
LOCATE
LTRIM
RIGHT
RTRIM
SPACE
SUBSTRING
UCASE
Example
SELECT emp_id FROM emp WHERE LCASE(emp_name) = 'karen jung'
Numeric Functions
ABS
ACOS
ASIN
ATAN
ATAN2
CEILING
COS
COT
DEGREES
EXP
FLOOR
HOUR
LOG
LOG10
MOD
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
TAN
TRUNCATE
Example
SELECT * FROM tbl1 WHERE MOD(col1, 100) = 0
Date Functions
CURDATE
CURTIME
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
MINUTE
MONTH
MONTHNAME
NOW
QUARTER
SECOND
WEEK
YEAR
Example
SELECT * FROM emp WHERE YEAR(hire_date) <1990
System Functions
DATABASE
IF
NULL
USER
Example
SELECT * FROM emp WHERE ename = USER()
The system scalar functions IF and NULL are SQL extensions.
IF allows you to enter different values depending on whether the condition is true or false. The syntax is:
IF(predicate, expression, expression)
For example, if you want to display a column with logical values as "True" or "False" instead of a binary representation, you would use the following SQL statement:
SELECT IF(logicalcol=1, "True", "False")
The system scalar function NULL allows you to set a column as null values. The syntax is:
NULL()
For example, the following SQL statement retrieves null values:
SELECT NULL() FROM emp
Conversion Function
CONVERT
Example
SELECT empno FROM emp WHERE hire_date > CONVERT('1993-01-02', SQL_DATE) + 30