Scalar Functions

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